Teoria PL/pgSQL

De wikijoan
Salta a la navegació Salta a la cerca

La guia més completa del llenguatge procedimental PL/pgsql està en la documentació oficial de PostgreSQL:

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.

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)

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

Obtingut de «http://wiki.joanillo.org/index.php?title=Teoria_PL/pgSQL&oldid=248557»