Teoria PL/pgSQL
La guia més completa del llenguatge procedimental PL/pgsql està en la documentació oficial de PostgreSQL:
- http://www.postgresql.org/docs/8.3/static/plpgsql.html
- http://www.postgresql.org/docs/8.3/static (index per a tot el manual)
Llibre Practical Postgres (John Worsley, Joshua Drake)
PostgreSQL cheat sheet (xuleta):
Com veuràs, hi ha moltes similituds amb allò que has estudiat amb el llenguatge PlSQL.
Contingut |
Instal.lació del llenguatge
Faig la primera prova, i surt el missatge ERROR: no existe el lenguaje «plpgsql»
postgres=# CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$ postgres$# BEGIN postgres$# RETURN subtotal * 0.06; postgres$# END; postgres$# $$ LANGUAGE plpgsql; ERROR: no existe el lenguaje «plpgsql» HINT: Usar CREATE LANGUAGE para instalar el lenguaje en la base de datos.
Ho solucionem:
postgres=# CREATE LANGUAGE plpgsql; CREATE LANGUAGE
Estructura d'un bloc
L'estructura d'un bloc PL/pgSQL és:
[ <<label>> ]
[ DECLARE
declarations ]
BEGIN
statements
END [ label ];
Declaració de variables
Les variables PL/pgSQL són de qualsevol del tipus de dades SQL, com ara integer, varchar, i char. Per exemple:
user_id integer; quantity numeric(5); url varchar; myrow tablename%ROWTYPE; myfield tablename.columnname%TYPE; arow RECORD;
la sintaxi general en la declaració d'una variable eś:
name [ CONSTANT ] type [ NOT NULL ] [ { DEFAULT | := } expression ];
Exemples:
quantity integer DEFAULT 32; url varchar := 'http://mysite.com'; user_id CONSTANT integer := 10;
Àlies per a paràmetres de funcions
Els paràmetres que li passem a les funcions els distingim amb els identificadors $1, $2, etc. Opcionalment podem declarar àlies per als paràmetres i així millorar la lectura del codi.
Hi ha dos maneres de crar un àlies. La millor manera és donar un nom al paràmetre a dins de la comanda CREATE FUNCTION:
CREATE OR REPLACE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
BEGIN
RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;
Però hi ha una altra manera, que és crear l'àlies de forma explícita:
CREATE OR REPLACE FUNCTION sales_tax(real) RETURNS real AS $$
DECLARE
subtotal ALIAS FOR $1;
BEGIN
RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;
Podem comprovar que funciona fent:
postgres=# select sales_tax(253);
sales_tax
-----------
15.18
(1 fila)
Els paràmetres també poden ser de sortida. La mateixa funció es pot reescriure:
CREATE OR REPLACE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
BEGIN
tax := subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;
Igual que féiem amb Oracle, els tipus es poden copiar:
user_id users.user_id%TYPE; name table_name%ROWTYPE;
Per eliminar la funció:
DROP FUNCTION sales_tax (real);
Sentències bàsiques
Assignació:
variable := expression; tax := subtotal * 0.06; my_record.user_id := 20;
Execució d'una comana SQL que no retorna res:
DECLARE
key TEXT;
delta INTEGER;
BEGIN
...
UPDATE mytab SET val = val + delta WHERE id = key;
UPDATE mytab SET val = val + $1 WHERE id = $2;
És una mala idea utilitzar noms per als paràmetres que siguin igual que valors de taules o camps.
Executar una query que retorna un sol valor: similar a l'Oracle:
SELECT select_expressions INTO [STRICT] target FROM ...;
SELECT * INTO myrec FROM emp WHERE empname = myname;
IF NOT FOUND THEN
RAISE EXCEPTION 'employee % not found', myname;
END IF;
però a PostgreSQL tenim la possibilitat d'utilitzar la clàusula STRICT. Si no es posa STRICT i la select retorna més d'una fila, no retorna error, senzillament agafa la primera fila.
Exemple complet per mostrar com funciona les excepcions i la clàusula STRICT:
DROP TABLE emp;
CREATE TABLE emp (
empname text,
salary integer,
last_date timestamp,
last_user text
);
INSERT INTO emp(empname,salary) VALUES('Pere',2000);
INSERT INTO emp(empname,salary) VALUES('Maria',2200);
INSERT INTO emp(empname,salary) VALUES('Maria',2300);
INSERT INTO emp(empname,salary) VALUES('Maria',2400);
CREATE OR REPLACE FUNCTION trobar_nom(myname varchar) RETURNS void AS $$
DECLARE
myrec emp%ROWTYPE;
BEGIN
SELECT * INTO myrec FROM emp WHERE empname = myname; --no utilitzem STRICT
IF FOUND THEN
RAISE NOTICE'employee % found', myname;
ELSE
RAISE EXCEPTION 'employee % not found', myname;
END IF;
END;
$$ LANGUAGE plpgsql;
# select trobar_nom('Pere');
NOTICE: employee Pere found
# select trobar_nom('Pol');
ERROR: employee Pol not found
# select trobar_nom('Maria');
NOTICE: employee Maria found
Ara utilitzem STRICT i provoca l'error que en Oracle coneixíem com TOO_MANY_ROWS
CREATE OR REPLACE FUNCTION trobar_nom(myname varchar) RETURNS void AS $$
DECLARE
myrec emp%ROWTYPE;
BEGIN
SELECT * INTO STRICT myrec FROM emp WHERE empname = myname; --utilitzem STRICT
IF FOUND THEN
RAISE NOTICE'employee % found', myname;
ELSE
RAISE EXCEPTION 'employee % not found', myname;
END IF;
END;
$$ LANGUAGE plpgsql;
# select trobar_nom('Maria');
ERROR: query returned more than one row
CONTEXT: PL/pgSQL function "trobar_nom" line 4 at SQL statement
Estructures de control
RETURN
RETURN expression;
RETURN en una expressió finalitza la funció i retorna el valor de l'expressió al qui l'ha cridat. Aquesta és la manera comuna, i s'utilitza en les funcions PL/pgSQL que no retornen un conjunt de valors.
RETURN NEXT i RETURN QUERY
RETURN NEXT expression; RETURN QUERY query;
Quan una funció PL/pgSQL es declara que retorni d'un tipus amb SETOF, el procediment que segueix és una mica diferent. En aquest cas, els elements individuals que es retornen s'especifiquen per una seqüència de comandes RETURN NEXT o RETURN QUERY, i finalment s'utilitza una comanda RETURN sense argument per indicar que la funció ha acabat la seva execució. RETURN NEXT es pot utilitzar tant per tipus de dades simples o compostos; amb tipus de dades compostos, el que es retorna és una taula de resultats. RETURN QUERY afegeix els resultats de l'execució d'una query al conjunt de resultats de la funció.
Un exemple:
CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
INSERT INTO foo VALUES (1, 2, 'three');
INSERT INTO foo VALUES (4, 5, 'six');
CREATE OR REPLACE FUNCTION getAllFoo() RETURNS SETOF foo AS
$BODY$
DECLARE
r foo%rowtype;
BEGIN
FOR r IN SELECT * FROM foo
WHERE fooid > 0
LOOP
-- can do some processing here
RETURN NEXT r; -- return current row of SELECT
END LOOP;
RETURN;
END
$BODY$
LANGUAGE 'plpgsql' ;
postgres=# SELECT * FROM getallfoo();
fooid | foosubid | fooname
-------+----------+---------
1 | 2 | three
4 | 5 | six
(2 filas)
Condicionals
IF boolean-expression THEN
statements
END IF;
- IF ... THEN
- IF ... THEN ... ELSE
- IF ... THEN ... ELSE IF
- IF ... THEN ... ELSIF ... THEN ... ELSE
- IF ... THEN ... ELSEIF ... THEN ... ELSE
IF v_user_id <> 0 THEN
UPDATE users SET email = v_email WHERE user_id = v_user_id;
END IF;
IF parentid IS NULL OR parentid = ''
THEN
RETURN fullname;
ELSE
RETURN hp_true_filename(parentid) || '/' || fullname;
END IF;
IF v_count > 0 THEN
INSERT INTO users_count (count) VALUES (v_count);
RETURN 't';
ELSE
RETURN 'f';
END IF;
IF demo_row.sex = 'm' THEN
pretty_sex := 'man';
ELSE
IF demo_row.sex = 'f' THEN
pretty_sex := 'woman';
END IF;
END IF;
IF number = 0 THEN
result := 'zero';
ELSIF number > 0 THEN
result := 'positive';
ELSIF number < 0 THEN
result := 'negative';
ELSE
-- hmm, the only other possibility is that number is null
result := 'NULL';
END IF;
LOOP i EXIT
[ <<label>> ]
LOOP
statements
END LOOP [ label ];
EXIT [ label ] [ WHEN boolean-expression ];
Exemples:
LOOP
-- some computations
IF count > 0 THEN
EXIT; -- exit loop
END IF;
END LOOP;
LOOP
-- some computations
EXIT WHEN count > 0; -- same result as previous example
END LOOP;
BEGIN
-- some computations
IF stocks > 100000 THEN
EXIT; -- causes exit from the BEGIN block
END IF;
END;
CONTINUE
CONTINUE [ label ] [ WHEN boolean-expression ];
LOOP
-- some computations
EXIT WHEN count > 100;
CONTINUE WHEN count < 50;
-- some computations for count IN [50 .. 100]
END LOOP;
WHILE
[ <<label>> ]
WHILE boolean-expression LOOP
statements
END LOOP [ label ];
exemple:
WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
-- some computations here
END LOOP;
WHILE NOT done LOOP
-- some computations here
END LOOP;
FOR
[ <<label>> ]
FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOP
statements
END LOOP [ label ];
FOR i IN 1..10 LOOP
-- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop
END LOOP;
FOR i IN REVERSE 10..1 LOOP
-- i will take on the values 10,9,8,7,6,5,4,3,2,1 within the loop
END LOOP;
FOR i IN REVERSE 10..1 BY 2 LOOP
-- i will take on the values 10,8,6,4,2 within the loop
END LOOP;
LOOP sobre els resultats d'una query
[ <<label>> ]
FOR target IN query LOOP
statements
END LOOP [ label ];
Exemple senzill:
CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
INSERT INTO foo VALUES (1, 2, 'three');
INSERT INTO foo VALUES (4, 5, 'six');
CREATE OR REPLACE FUNCTION veure_foo() RETURNS integer AS $$
DECLARE
registre_foo RECORD;
BEGIN
FOR registre_foo IN SELECT * FROM foo LOOP
-- ara registre_foo té una fila de la taula foo
RAISE NOTICE 'Resultat: %', registre_foo.fooname;
END LOOP;
RETURN 1;
END;
$$ LANGUAGE plpgsql;
postgres=# select * from veure_foo();
NOTICE: Resultat: three
NOTICE: Resultat: six
veure_foo
-----------
1
(1 fila)
</pre> Exemple difícil:
CREATE FUNCTION cs_refresh_mviews() RETURNS integer AS $$
DECLARE
mviews RECORD;
BEGIN
PERFORM cs_log('Refreshing materialized views...');
FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP
-- Now "mviews" has one record from cs_materialized_views
PERFORM cs_log('Refreshing materialized view ' || quote_ident(mviews.mv_name) || ' ...');
EXECUTE 'TRUNCATE TABLE ' || quote_ident(mviews.mv_name);
EXECUTE 'INSERT INTO ' || quote_ident(mviews.mv_name) || ' ' || mviews.mv_query;
END LOOP;
PERFORM cs_log('Done refreshing materialized views.');
RETURN 1;
END;
$$ LANGUAGE plpgsql;
Atrapant errors
[ <<label>> ]
[ DECLARE
declarations ]
BEGIN
statements
EXCEPTION
WHEN condition [ OR condition ... ] THEN
handler_statements
[ WHEN condition [ OR condition ... ] THEN
handler_statements
... ]
END;
INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
BEGIN
UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
x := x + 1;
y := x / 0;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE 'caught division_by_zero';
RETURN x;
END;
En el primer exemple veiem com es pot controlar l'error de violació de clau primària:
CREATE TABLE db (a INT PRIMARY KEY, b TEXT); CREATE FUNCTION provem_insertar(key INT, data TEXT) RETURNS VOID AS $$ BEGIN INSERT INTO db(a,b) VALUES (key, data); RETURN; EXCEPTION WHEN unique_violation THEN RAISE NOTICE 'violació de clau primària'; END;$$ LANGUAGE plpgsql; postgres=# SELECT provem_insertar(2, 'Maria'); provem_insertar ----------------- (1 fila) postgres=# SELECT provem_insertar(2, 'Pere'); NOTICE: violació de clau primària provem_insertar -----------------
El segon exemple és una mica més complicat:
CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
LOOP
-- first try to update the key
UPDATE db SET b = data WHERE a = key;
IF found THEN
RETURN;
END IF;
-- not there, so try to insert the key
-- if someone else inserts the same key concurrently,
-- we could get a unique-key failure
BEGIN
INSERT INTO db(a,b) VALUES (key, data);
RETURN;
EXCEPTION WHEN unique_violation THEN
-- do nothing, and loop to try the UPDATE again
END;
END LOOP;
END;
$$
LANGUAGE plpgsql;
SELECT merge_db(1, 'david');
postgres=# select * from db;
a | b
---+-------
1 | david
SELECT merge_db(1, 'dennis');
postgres=# select * from db;
a | b
---+--------
1 | dennis
(1 fila)
Cursors
Declaració dels cursors:
name [ [ NO ] SCROLL ] CURSOR [ ( arguments ) ] FOR query;
DECLARE
curs1 refcursor;
curs2 CURSOR FOR SELECT * FROM tenk1;
curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;
Els cursors s'han d'obrir. El cas normal és OPEN FOR query:
OPEN unbound_cursor [ [ NO ] SCROLL ] FOR query; OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
Però també pot ser OPEN FOR execute:
OPEN unbound_cursor [ [ NO ] SCROLL ] FOR EXECUTE query_string; OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);
Si en la declaració del cursor hem declarat la query, aleshores només cal obrir-lo:
OPEN curs2; OPEN curs3(42);
Un cop obert, el cursor s'ha d'obrir amb FETCH:
FETCH [ direction { FROM | IN } ] cursor INTO target;
Exemples:
FETCH curs1 INTO rowvar; FETCH curs2 INTO foo, bar, baz; FETCH LAST FROM curs3 INTO x, y; FETCH RELATIVE -2 FROM curs4 INTO x;
i amb la instrucció MOVE ens movem pel cursor:
MOVE [ direction { FROM | IN } ] cursor;
Exemples:
MOVE curs1; MOVE LAST FROM curs3; MOVE RELATIVE -2 FROM curs4;
Igual que en Oracle, també podem fer operacions d'actualització mentre ens movem per un cursor: UPDATE/DELETE WHERE CURRENT OF
UPDATE table SET ... WHERE CURRENT OF cursor; DELETE FROM table WHERE CURRENT OF cursor; UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;
Finalment el cursor s'ha de tancar: CLOSE
CLOSE cursor;
Un exemple senzill d'utilització un cursro (declaració, obertura, recorregut, tancament), amb dues maneres de fer el bucle:
CREATE TABLE test (col text);
INSERT INTO test VALUES ('123');
INSERT INTO test VALUES ('234');
INSERT INTO test VALUES ('345');
CREATE OR REPLACE FUNCTION visualitza_test() RETURNS VOID AS $$
DECLARE
cur CURSOR FOR SELECT col FROM test;
camp text;
BEGIN
OPEN cur;
FETCH cur INTO camp;
WHILE FOUND LOOP
RAISE NOTICE 'Result %', camp;
FETCH cur INTO camp;
END LOOP;
END
$$ LANGUAGE 'plpgsql';
select visualitza_test();
CREATE OR REPLACE FUNCTION visualitza_test() RETURNS VOID AS '
DECLARE
cur CURSOR FOR SELECT col FROM test;
camp text;
BEGIN
OPEN cur;
LOOP
FETCH cur INTO camp;
IF NOT FOUND THEN
EXIT;
END IF;
RAISE NOTICE ''Result %'', camp;
END LOOP;
CLOSE cur;
RETURN;
END;
' LANGUAGE plpgsql;
select visualitza_test();
Un exemple més difícil: una funció pot retornar un cursor:
CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
OPEN $1 FOR SELECT col FROM test;
RETURN $1;
END;
' LANGUAGE plpgsql;
BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;
El resultat per pantalla és:
postgres=# BEGIN;
BEGIN
postgres=# SELECT reffunc('funccursor');
reffunc
------------
funccursor
(1 fila)
postgres=# FETCH ALL IN funccursor;
col
-----
123
234
345
(3 filas)
Fixem-nos com l'expressió FETCH ALL s'encarrega automàticament de visualitzar tot el contingut del cursor.
Triggers. Disparadors
Un trigger és de fet un procediment. Un procediment de disparador es crea amb CREATE FUNCTION, declarant-lo com a funció sense arguments i retornant el tipus trigger. La funció es crea sense arguments encara que els esperi. Els arguments del trigger es passes via TG_ARGV (com es veu més avall).
Quan es crida una funció PL/pgSQL com a trigger, es creen automàticament algunes variables especials, que són:
- NEW: Data type RECORD; variable que conté la nova fila de la bd actualitzada amb el INSERT/UPDATE en triggers a nivell de fila. Aquesta variable és NULL en trigger de sentència.
- OLD: Data type RECORD; variable que conté l'antiga fila de la bd actualitzada amb el UPDATE/DELETE en triggers a nivell de fila. Aquesta variable és NULL en trigger de sentència.
- TG_NAME: Data type name; variable que conté el nom del trigger que s'ha disparat.
- TG_WHEN: Data type text; un string que conté BEFORE o AFTER depenent de la definició del trigger.
- TG_LEVEL: Data type text; un string que conté ROW o STATEMENT depenent de la definició del trigger.
- TG_OP: Data type text; un string que conté INSERT, UPDATE, or DELETE dient-nos quina operació és la que ha disparat el trigger.
- TG_RELID: Data type oid; el ID de la taula que ha provocat el disparo del trigger.
- TG_RELNAME: Data type name; el nom de la taula que ha provocat el disparo del trigger. Està deprecated. Utilitzar TG_TABLE_NAME en el seu lloc.
- TG_TABLE_NAME: Data type name; el nom de la taula que ha provocat el disparo del trigger.
- TG_TABLE_SCHEMA: Data type name; el nom de l'esquema (usuari) de la taula que ha provocat el disparo del trigger.
- TG_NARGS: Data type integer; el número d'arguments que s'han passat al trigger en la sentència CREATE TRIGGER.
- TG_ARGV[]: Data type array of text; els arguments de la sentència CREATE TRIGGER. L'index es compta des de 0.
Exemple:
DROP TABLE emp;
CREATE TABLE emp (
empname text,
salary integer,
last_date timestamp,
last_user text
);
CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
BEGIN
-- Check that empname and salary are given
IF NEW.empname IS NULL THEN
RAISE EXCEPTION 'empname cannot be null';
END IF;
IF NEW.salary IS NULL THEN
RAISE EXCEPTION '% cannot have null salary', NEW.empname;
END IF;
-- Who works for us when she must pay for it?
IF NEW.salary < 0 THEN
RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
END IF;
-- Remember who changed the payroll when
NEW.last_date := current_timestamp;
NEW.last_user := current_user;
RETURN NEW;
END;
$emp_stamp$ LANGUAGE plpgsql;
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
Nota: les variables current_timestamp i current_user són variables globals de la base de dades. N'hi ha 5: current_date, current_time, current_timestamp, current_user, localtime. Pots comprovar el seu funcionament amb:
postgres# select current_time; postgres# select current_user;
Joc de proves:
INSERT INTO emp(empname,salary) VALUES('Rita',1250);
INSERT INTO emp(empname,salary) VALUES('Maria',980);
INSERT INTO emp(empname,salary) VALUES('Pere',1040);
postgres=# select * from emp;
empname | salary | last_date | last_user
---------+--------+----------------------------+-----------
Rita | 1250 | 2009-10-27 12:09:05.320801 | postgres
Maria | 980 | 2009-10-27 12:09:05.360962 | postgres
Pere | 1040 | 2009-10-27 12:09:06.113688 | postgres
INSERT INTO emp(empname,salary) VALUES('Joan',-1200);
ERROR: Joan cannot have a negative salary
Disparador per auditar
Una dels usos típics d'utilització dels disparadors es per auditar tota l'activitat que està succeïnt a la base de dades. Anem a veure amb aquest exemple com podem auditar les operacions que fem sobre la taula emp:
DROP TABLE emp;
CREATE TABLE emp (
empname text NOT NULL,
salary integer
);
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
stamp timestamp NOT NULL,
userid text NOT NULL,
empname text NOT NULL,
salary integer
);
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
BEGIN
--
-- Create a row in emp_audit to reflect the operation performed on emp,
-- make use of the special variable TG_OP to work out the operation.
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
RETURN NEW;
END IF; --Procedure For Maintaining A Summary Table
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$emp_audit$ LANGUAGE plpgsql;
CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();
Joc de proves:
INSERT INTO emp(empname,salary) VALUES('Jordi',1500);
UPDATE emp set salary=1800 WHERE empname='Jordi';
DELETE FROM emp WHERE empname='Jordi';
postgres=# select * from emp_audit;
operation | stamp | userid | empname | salary
-----------+----------------------------+----------+---------+--------
I | 2009-10-27 12:16:56.338022 | postgres | Jordi | 1500
U | 2009-10-27 12:17:12.388253 | postgres | Jordi | 1800
D | 2009-10-27 12:17:13.422631 | postgres | Jordi | 1800
(3 filas)
Un altre exemple: Procediment per mantenir una taula de resum de les vendes:
Explicació: la idea de l'exercici és que tens la taula sales_fact (que seria equivalent a la taula VENDES o FACTURES), i hi ha una taula que fa el paper de resum de vendes: sales_summary_bytime (resum de vendes agrupat per temps). L'objectiu del trigger maint_sales_summary_bytime (manteniment del resum de vendes per mes) és que quan fem una inserció, modificació o eliminació d'una venda (en la taula sales_fact), s'ha d'actualitzar de forma automàtica el resum en la taula sales_summary_bytime
--
-- Main tables - time dimension and sales fact.
--
CREATE TABLE time_dimension (
time_key integer NOT NULL,
day_of_week integer NOT NULL,
day_of_month integer NOT NULL,
month integer NOT NULL,
quarter integer NOT NULL,
year integer NOT NULL
);
CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);
CREATE TABLE sales_fact (
time_key integer NOT NULL,
product_key integer NOT NULL,
store_key integer NOT NULL,
amount_sold numeric(12,2) NOT NULL,
units_sold integer NOT NULL,
amount_cost numeric(12,2) NOT NULL
);
CREATE INDEX sales_fact_time ON sales_fact(time_key);
--
-- Summary table - sales by time.
--
CREATE TABLE sales_summary_bytime (
time_key integer NOT NULL,
amount_sold numeric(15,2) NOT NULL,
units_sold numeric(12) NOT NULL,
amount_cost numeric(15,2) NOT NULL
);
CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);
--
-- Function and trigger to amend summarized column(s) on UPDATE, INSERT, DELETE.
--
CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER AS $maint_sales_summary_bytime$
DECLARE
delta_time_key integer;
delta_amount_sold numeric(15,2);
delta_units_sold numeric(12);
delta_amount_cost numeric(15,2);
BEGIN
-- Work out the increment/decrement amount(s).
IF (TG_OP = 'DELETE') THEN
delta_time_key = OLD.time_key;
delta_amount_sold = -1 * OLD.amount_sold;
delta_units_sold = -1 * OLD.units_sold;
delta_amount_cost = -1 * OLD.amount_cost;
ELSIF (TG_OP = 'UPDATE') THEN
-- forbid updates that change the time_key -
-- (probably not too onerous, as DELETE + INSERT is how most
-- changes will be made).
IF ( OLD.time_key != NEW.time_key) THEN
RAISE EXCEPTION 'Update of time_key : % -> % not allowed', OLD.time_key, NEW.time_key;
END IF;
delta_time_key = OLD.time_key;
delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
delta_units_sold = NEW.units_sold - OLD.units_sold;
delta_amount_cost = NEW.amount_cost - OLD.amount_cost;
ELSIF (TG_OP = 'INSERT') THEN
delta_time_key = NEW.time_key;
delta_amount_sold = NEW.amount_sold;
delta_units_sold = NEW.units_sold;
delta_amount_cost = NEW.amount_cost;
END IF;
-- Insert or update the summary row with the new values.
<<insert_update>>
LOOP
UPDATE sales_summary_bytime
SET amount_sold = amount_sold + delta_amount_sold,
units_sold = units_sold + delta_units_sold,
amount_cost = amount_cost + delta_amount_cost
WHERE time_key = delta_time_key;
EXIT insert_update WHEN found;
BEGIN
INSERT INTO sales_summary_bytime (
time_key,
amount_sold,
units_sold,
amount_cost)
VALUES (
delta_time_key,
delta_amount_sold,
delta_units_sold,
delta_amount_cost
);
EXIT insert_update;
EXCEPTION
WHEN UNIQUE_VIOLATION THEN
-- do nothing
END;
END LOOP insert_update;
RETURN NULL;
END;
$maint_sales_summary_bytime$ LANGUAGE plpgsql;
CREATE TRIGGER maint_sales_summary_bytime
AFTER INSERT OR UPDATE OR DELETE ON sales_fact
FOR EACH ROW EXECUTE PROCEDURE maint_sales_summary_bytime();
Joc de proves:
Fem quatre vendes: INSERT INTO sales_fact VALUES(1,1,1,10,3,15); INSERT INTO sales_fact VALUES(1,2,1,20,5,35); INSERT INTO sales_fact VALUES(2,2,1,40,15,135); INSERT INTO sales_fact VALUES(2,3,1,10,1,13); i podem veure el resum de les vendes: SELECT * FROM sales_summary_bytime; Ara eliminem una de les vendes i la informació del resum s'actualitza: DELETE FROM sales_fact WHERE product_key = 1; SELECT * FROM sales_summary_bytime; Ara no veig que l'operació es faci bé... UPDATE sales_fact SET units_sold = units_sold * 2; SELECT * FROM sales_summary_bytime;
Exportar codi des de Oracle Pl/SQL
Com podem veure, els conceptes de procediment, funció, cursor, excepcions, triggers són els mateixos amb Oracle i PostgreSQL. Per convertir codi que tenim a Oracle (PL/SQL) a PostgreSQL (PL/pgSQL), sense ser evident, és relativament fàcil. Un exemple:
codi PL/SQL:
CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
v_version varchar)
RETURN varchar IS
BEGIN
IF v_version IS NULL THEN
RETURN v_name;
END IF;
RETURN v_name || '/' || v_version;
END;
/
show errors;
codi PL/pgSQL:
CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
v_version varchar)
RETURNS varchar AS $$
BEGIN
IF v_version IS NULL THEN
RETURN v_name;
END IF;
RETURN v_name || '/' || v_version;
END;
$$ LANGUAGE plpgsql;
creat per Joan Quintana Compte, octubre 2009
