Here are only a few functions to demonstrate how easy PL/pgSQL functions can be written. For more complex examples the programmer might look at the regression test for PL/pgSQL.
One painful detail of writing functions in PL/pgSQL is the handling of single quotes. The functions source text on 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 qoutes as in the examples below should be used. Any solution for this in future versions of Postgres will be upward compatible.
The following two PL/pgSQL functions are identical to their counterparts from the C language function discussion.
CREATE FUNCTION add_one (int4) RETURNS int4 AS ' BEGIN RETURN $1 + 1; END; ' LANGUAGE 'plpgsql';
CREATE FUNCTION concat_text (text, text) RETURNS text AS ' BEGIN RETURN $1 || $2; END; ' LANGUAGE 'plpgsql';
Again it is the PL/pgSQL equivalent to the example from The C functions.
CREATE FUNCTION c_overpaid (EMP, int4) RETURNS bool 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';
This trigger ensures, that any time a row is inserted or updated in the table, the current username and time are stamped into the row. And it ensures that an employees name is given and that the salary is a positive value.
CREATE TABLE emp ( empname text, salary int4, last_date datetime, last_user name); 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 := getpgusername(); RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.