PL/pgSQL

Structure of a PL/pgSQL Procedure

PL/pgSQL is block structured:

[ Label ]
[ DECLARE
     declarations ]
BEGIN
   statements;
END;

CREATE FUNCTION somefunc() RETURNS INTEGER AS '
DECLARE
   quantity INTEGER := 30;
BEGIN
   RAISE NOTICE ''Quantity here is %'',quantity;  -- Quantity here is 30
   quantity := 50;
   --
   -- Create a sub-block
   --
   DECLARE
      quantity INTEGER := 80;
   BEGIN
      RAISE NOTICE ''Quantity here is %'',quantity;  -- Quantity here is 80
   END;

   RAISE NOTICE ''Quantity here is %'',quantity;  -- Quantity here is 50
END;
' LANGUAGE 'plpgsql';

Variables

PL/pgSQL has all SQL types. It is possible to create tuple variables using %ROWTYPE attribute.

Example showing this and also control structures:

DROP FUNCTION Test(INTEGER);

CREATE FUNCTION Test(INTEGER) RETURNS INTEGER AS '
DECLARE

   StudRec  Student%ROWTYPE;
   TransRec Transcript%ROWTYPE;
   Count    INTEGER := 0;
   Id       ALIAS FOR $1;

BEGIN

   -- SELECT result must be a single tuple.

   SELECT INTO StudRec *
   FROM  Student S
   WHERE S.Id = Id;

   -- Aggregate functions appear not to work.  Manually iterate over
   -- entire result set to compute count.

   FOR TransRec IN SELECT * FROM Transcript T
   WHERE StudRec.Id = T.StuId
   LOOP
      Count := Count + 1;
   END LOOP;

   IF Count < 10 THEN
      RAISE NOTICE ''% has taken too few courses'', StudRec.Name;
   END IF;

   RETURN count;
END;
' LANGUAGE 'plpgsql';

SELECT Test(666666666);

Features Specific to Trigger Procedures

Trigger procedures must be a function with no parameters and a return type of OPAQUE.

Special variables automatically created in the top-level block:

1. NEW --- new tuple value on UPDATE/INSERT row level triggers.
2. OLD --- old tuple value on UPDATE/DELETE row level triggers.

The procedure must either return NULL (or execute RAISE EXCEPTION) or a tuple matching the structure of the relation the trigger was called on.

1. BEFORE triggers return NULL to signal that the operation for this tuple should be skipped, RAISE EXCEPTION to abort the transaction, return a modified result, or do nothing (return NEW unchanged) to allow the intended result.
2. AFTER triggers can return NULL with no effect.

Example:

CREATE FUNCTION RaiseCheck () RETURNS OPAQUE AS '
   BEGIN
      IF NEW.Salary > 1.05 * OLD.Salary THEN
         -- Excessive salary raise - limit it.
         RAISE NOTICE ''% given an excessive raise'', OLD.EmpName;
         NEW.Salary := 1.05 * OLD.Salary;
      END IF;
      -- Salary increase OK, proceed with transaction.
      RETURN NEW;
   END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER LimitRaises BEFORE UPDATE ON Employee 
   FOR EACH ROW EXECUTE PROCEDURE RaiseCheck();