sql-wyzwalacze-reguly-funkcje.pdf

(178 KB) Pobierz
PostgreSQL
1
Bazy Danych
Zaawansowane funkcje SQL.
˙
Reguły, funkcje uzytkownika w
PostgreSQL, wyzwalacze
Antoni Lig˛ za, Marcin Szpyrka
e
´
Sławomir Lichon
Wykorzystano materiały:
http://www.postgresql.org/docs/8.3/interactive/
extend.html
c Antoni Lig˛ za, Marcin Szpyrka, Sławomir Licho´
e
n
PostgreSQL
2
Drzewo zapytania
Drzewo zapytania
— (ang. query tree) to wewn˛ trzna strukturalna reprezenta-
e
cja zapytania SQL wygenerowana przez parser na podstawie analizy komendy
˙
SQL uzytkownika. Komenda SQL jest defragmentowana; wygenerowane frag-
menty sa przechowywane oddzielnie.
˛
System reguł
— (ang. rule system) jest umieszczony pomi˛ dzy
parserem
a
e
planerem.
Jest to w istocie mechanizm typu
term rewriting;
przetwarza on jedno
drzewo zapytania na inne.
Komenda SQL jest dzielona na fragmenty:
•
the command type
—
SELECT, INSERT, UPDATE, DELETE,
•
the range table
— tablice na których działa zapytanie (po
FROM),
•
the result relation
UPDATE, DELETE,
—
tablica
docelowa
dla
INSERT,
˙ n
•
the target list
— lista wyraze´ tworzacych wynik zapytania (po
˛
SELECT),
˙
•
the qualification
— wyrazenie logiczne okre´lajace kiedy opera-
s ˛
˙
cj˛ nalezy wykona´ (analogiczne do
WHERE),
e
c
•
the join tree
— struktura drzewa łaczenia tablic ()po
FROM,
˛
•
the others
— inne cz˛ sci zapytania (np. po
ORDER BY).
e´
c Antoni Lig˛ za, Marcin Szpyrka, Sławomir Licho´
e
n
PostgreSQL
3
Reguły
Widoki w PostgreSQL sa definiowane komenda
CREATE VIEW
wraz z we-
˛
˛
wn˛ trzna komenda
SELECT.
e
˛
˛
Deklaracja
CREATE VIEW
stanowi w istocie makro ’podmieniajace’ instruk-
˛
cj˛ tworzenia widoku na tworzenie tabeli i reguły typu
ON SELECT.
e
W istocie, deklaracja:
CREATE VIEW widok AS SELECT * FROM tabela;
˙
jest realizowana poprzez uzycie nowej tabeli i reguły:
CREATE TABLE widok (<lista kolumn taka jak w tabeli>);
CREATE RULE "_RETURN" AS
ON SELECT TO widok DO INSTEAD
SELECT * FROM tabela;
Reguły w PostgreSQL dostarczaja mechanizmu
podmiany
komendy SQL na
˛
inna komend˛ .
˛
e
Reguły
ON SELECT
sa stosowane w ostatniej fazie przetwarzania kodu zapy-
˛
tania.
˙
Mozna w nich zastosowa´ jedynie pojedyncza instrukcj˛
SELECT
z opcja
DO
c
˛
e
˛
INSTEAD
(podmiana).
Reguły pozwalaja na zdefiniowanie instrukcji SQL, które powinny by´ automa-
˛
c
˙
tycznie dołaczone do podawanych przez uzytkownika polece´ lub zamiast nich
˛
n
by´ wstawione i wykonane.
c
Reguły realizowane sa przez standardowa sciezk˛ przetwarzania instrukcji
˛
˛ ´ ˙ e
˙
SQL. Sa jednak szybsze gdyz korzystaja z optymalizatora zapyta´ i mniej ob-
˛
˛
n
˙
˙
˙
ciazaja system niz procedury składowane. Maja za to duzo mniejsze mozliwo-
˛˙ ˛
˛
´
sci.
c Antoni Lig˛ za, Marcin Szpyrka, Sławomir Licho´
e
n
PostgreSQL
4
Składnia definicji reguł
Składnia definicji reguły wyglada nast˛ pujaco:
˛
e ˛
CREATE
AS
TO
DO
[ OR REPLACE ] RULE nazwa
ON <event>
tab [ WHERE warunek ]
[ ALSO | INSTEAD ] { NOTHING | zapytanie |
( zapytanie ; zapytanie ... ) }
INSERT,
˙
Mozliwe warto´ci dla znacznika
event
to
SELECT
oraz
s
UPDATE,DELETE.
Dla
INSERT, UPDATE,DELETE
reguły pozwalaja na:
˛
•
DO ALSO
— dołaczanie pewnych instrukcji do innych instrukcji (kreowa-
˛
nie sekwencji instrukcji),
•
DO INSTEAD
— podmian˛ instrukcji na inna/inne.
e
˛
W zapisie definicji zdarzenie wraz z warunkiem okre´laja kiedy reguła powinna
s ˛
by´ zastosowana.
c
˙
Polecenie moze by´ pojedyncza instrukcja SQL, bad´ ich ciagiem uj˛ tym w
c
˛
˛
˛z
˛
e
nawiasy okragłe.
˛
Słowo kluczowe
INSTEAD
i
ALSO
okre´la czy reguła ma by´ wykonana za-
s
c
˙
miast, czy tez wraz ze wskazana operacja.
˛
˛
˙
Wewnatrz reguły, mozna korzysta´ z pewnych predefiniowanych pseudo-relacji
˛
c
NEW
i
OLD,
które reprezentuja modyfikowane rekordy, przed i po wykonaniu
˛
instrukcji modyfikacji.
˙
Reguły mozemy stosowa´ zarówno do tabel jak i do widoków.
c
˙
Aby na widoku były mozliwe operacje modyfikowania danych, musza by´
˛ c
utworzone odpowiednie reguły z ustawionym znacznikiem
INSTEAD.
W ra-
zie ich braku wystapi bład. Podobnie system zachowa si˛ jak zostana wykryte
˛
˛
e
˛
zap˛ tlenia.
e
c Antoni Lig˛ za, Marcin Szpyrka, Sławomir Licho´
e
n
PostgreSQL
5
Przykłady reguł
Wy´wietlanie kontrolne po zmianach (trigger-agr.sql):
s
CREATE RULE ragr
AS ON UPDATE TO prac
DO ALSO
-- INSTEAD
SELECT stanowisko, count(*) AS liczba_prac,
avg(pobory) as ´rednia, sum(pobory) AS Suma
S
FROM prac
GROUP BY stanowisko;
Podmiana (trigger-rule-select-agr.sql)
CREATE TABLE agrrule
(stanowisko
varchar(24),
liczba_prac
bigint,
´rednia
S
numeric,
Suma
numeric
);
CREATE RULE "_RETURN"
AS ON SELECT TO agrrule
DO INSTEAD
SELECT stanowisko, count(*) AS liczba_prac,
avg(pobory) as ´rednia, sum(pobory) AS Suma
S
FROM prac
GROUP BY stanowisko;
c Antoni Lig˛ za, Marcin Szpyrka, Sławomir Licho´
e
n

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika:

Zgłoś jeśli naruszono regulamin