Php-PgSql Example of Function

Examples

PL/pgSQL can be used to define trigger procedures. A trigger procedure is created with the CREATE FUNCTION command as a function with no arguments and a return type of OPAQUE. Note that the function must be declared with no arguments even if it expects to receive arguments specified in CREATE TRIGGER --- trigger arguments are passed via TG_ARGV, as described below.

When a PL/pgSQL function is called as a trigger, several special variables are created automatically in the top-level block. They are:

NEW

Data type RECORD; variable holding the new database row for INSERT/UPDATE
operations in ROW level triggers.

OLD

Data type RECORD; variable holding the old database row for UPDATE/DELETE
operations in ROW level triggers.

TG_NAME

Data type name; variable that contains the name of the trigger actually
fired.

TG_WHEN

Data type text; a string of either
BEFORE or AFTER
depending on the trigger's definition.

TG_LEVEL

Data type text; a string of either
ROW or STATEMENT depending on the
trigger's definition.

TG_OP

Data type text; a string of
INSERT, UPDATE
or DELETE telling
for which operation the trigger is fired.

TG_RELID

Data type oid; the object ID of the table that caused the
trigger invocation.

TG_RELNAME

Data type name; the name of the table that caused the trigger
invocation.

TG_NARGS

Data type integer; the number of arguments given to the trigger
procedure in the CREATE TRIGGER statement.

TG_ARGV[]

Data type array of text; the arguments from
the CREATE TRIGGER statement.
The index counts from 0 and can be given as an expression. Invalid
indices (< 0 or >= tg_nargs) result in a NULL value.

A trigger function must return either NULL or a record/row value having exactly the structure of the table the trigger was fired for. Triggers fired BEFORE may return NULL to signal the trigger manager to skip the rest of the operation for this row (ie, subsequent triggers
are not fired, and the INSERT/UPDATE/DELETE does not occur for this row). If a non-NULL value is returned then the operation proceeds with that row value. Note that returning a row value different from the original value of NEW alters the row that will be inserted or updated.
It is possible to replace single values directly in NEW and return that, or to build a complete new record/row to return.

The return value of a trigger fired AFTER is ignored; it may as well always return a NULL value. But an AFTER trigger can still abort the operation by raising an error.

Example 23-1. A PL/pgSQL Trigger Procedure Example

This example trigger ensures that any time a row is inserted or updated in the table, the current user name and time are stamped into the row. And it ensures that an employee's name is given and that the salary is a positive value.

CREATE TABLE emp (
   empname text,
   salary integer,
   last_date timestamp,
   last_user text
);

CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS '
   BEGIN
       -- Check that empname and salary are given
       IF NEW.empname ISNULL THEN
           RAISE EXCEPTION ''empname cannot be NULL value'';
       END IF;
       IF NEW.salary ISNULL THEN
           RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname;
       END IF;

       -- Who works for us when she must pay for?
       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 := ''now'';
       NEW.last_user := current_user;
       RETURN NEW;
   END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
   FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
    Here are only a few functions to demonstrate how easy it is to
 write PL/pgSQL
 functions. For more complex examples the programmer
 might look at the regression test for PL/pgSQL.
    One painful detail in writing functions in PL/pgSQL is the handling of single quotes. The function's source text in CREATE FUNCTION must
 be a literal string. Single quotes inside of literal strings must be either doubled or quoted with a backslash. We are still looking for
 an elegant alternative. In the meantime, doubling the single quotes as in the examples below should be used. Any solution for this
 in future versions of PostgreSQL will be forward compatible.
Example 2. A Simple PL/pgSQL Function to Increment an Integer
  The following two PL/pgSQL functions are identical to their counterparts from the C language function discussion. This
  function receives an integer and increments it by one, returning the incremented value.
 
CREATE FUNCTION add_one (integer) RETURNS INTEGER AS '
 BEGIN
     RETURN $1 + 1;
 END;
' LANGUAGE 'plpgsql';
Example 3. A Simple PL/pgSQL Function to Concatenate Text
     This function receives two text parameters and returns the result of concatenating them.
 
CREATE FUNCTION concat_text (TEXT, TEXT) RETURNS TEXT AS '
 BEGIN
     RETURN $1 || $2;
 END;
' LANGUAGE 'plpgsql';
Example 4. A PL/pgSQL Function on Composite Type
     In this example, we take EMP (a table) and an integer as arguments to our function, which returns
  a boolean. If the salary field of the EMP table is NULL, we return f. Otherwise we compare with
  that field with the integer passed to the function and return the boolean result of the comparison (t
  or f). This is the PL/pgSQL equivalent to the example from the C functions.
 
CREATE FUNCTION c_overpaid (EMP, INTEGER) RETURNS BOOLEAN AS '
 DECLARE
     emprec ALIAS FOR $1;
     sallim ALIAS FOR $2;
 BEGIN
     IF emprec.salary ISNULL THEN
         RETURN ''f'';
     END IF;
     RETURN emprec.salary > sallim;
 END;
' LANGUAGE 'plpgsql';