Quick Guide to writing PLPGSQL Functions: Part 3 - NOTICES, RECURSION, and more   

RAISE

RAISE Notices in plpgsql are generally used for two reasons:

• As a simple debugging tool to output state variables in a function call.
• As a WARNING to a user to inform them of important things such as this function is deprecated and should not be used or they are using something in an incorrect way.

A simple example of notices and recursion is shown below. Admittedly I couldn't come up with a more pointless example to demonstrate recursion:

CREATE OR REPLACE FUNCTION fnsomefunnote(param_numcount integer)
  RETURNS integer AS
$$
DECLARE
BEGIN
    IF param_numcount > 0 THEN
        RAISE NOTICE 'Yo there I''m number %, next: %', param_numcount, param_numcount -1;
        RETURN fnsomefunnote(param_numcount - 1);
    ELSE
        RETURN param_numcount;
    END IF;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE;

SELECT fnsomefunnote(4);

Returns 0 and also notices - if you are looking at this in PgAdminIII 
you shall see this on the messages tab

NOTICE:  Yo there I'm number 4, next: 3
NOTICE:  Yo there I'm number 3, next: 2
CONTEXT:  PL/pgSQL function "fnsomefunnote" line 5 at RETURN
NOTICE:  Yo there I'm number 2, next: 1
CONTEXT:  PL/pgSQL function "fnsomefunnote" line 5 at RETURN
PL/pgSQL function "somefunnote" line 5 at RETURN
NOTICE:  Yo there I'm number 1, next: 0
CONTEXT:  PL/pgSQL function "fnsomefunnote" line 5 at RETURN
PL/pgSQL function "fnsomefunnote" line 5 at RETURN

RAISE also has other variants namely DEBUG(1-5), LOG, INFO, EXCEPTION

DEBUG, LOG, and INFO are just different levels of NOTICE and only vary depending on which logs they get written to and if they get written to client. By default NOTICE is always written to the client. These are controlled by the postgresql.conf client_min_messages and log_min_messages.

RAISE EXCEPTION is slightly different. It both displays an error message and halts further excecution of the stored function.

Below is a slightly different variant of the above: Also note here we follow the general practice of having a single point of return. Having a single point of return tends to make your code easier to read and debug.

CREATE OR REPLACE FUNCTION fnsomemorefunnote(param_numcount integer)
  RETURNS integer AS
$$
DECLARE result integer;
BEGIN
    IF param_numcount < 0 THEN
        RAISE EXCEPTION 'Negative numbers are not allowed';
    ELSIF param_numcount > 0 THEN
        RAISE NOTICE 'Yo there I''m number %, next: %', param_numcount, param_numcount -1;
        result := fnsomemorefunnote(param_numcount - 1);
    ELSE
        RAISE INFO 'Alas we are at the end of our journey';
        result := param_numcount;
    END IF;
    RETURN result;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE;


--------
SELECT fnsomemorefunnote(n)
FROM generate_series(-1,2) As n;

ERROR:  Negative numbers are not allowed

********** Error **********

ERROR: Negative numbers are not allowed
SQL state: P0001
---------

--no result is returned--


SELECT fnsomemorefunnote(n)
FROM generate_series(0,2) As n;
--result--
0
0
0


--messages--
INFO:  Alas we are at the end of our journey
NOTICE:  Yo there I'm number 1, next: 0
INFO:  Alas we are at the end of our journey
CONTEXT:  PL/pgSQL function "fnsomemorefunnote" line 7 at assignment
NOTICE:  Yo there I'm number 2, next: 1
NOTICE:  Yo there I'm number 1, next: 0
CONTEXT:  PL/pgSQL function "fnsomemorefunnote" line 7 at assignment
INFO:  Alas we are at the end of our journey
CONTEXT:  PL/pgSQL function "fnsomemorefunnote" line 7 at assignment
PL/pgSQL function "fnsomemorefunnote" line 7 at assignment

Total query runtime: 16 ms.
3 rows retrieved.

Storing values in Temp variables, FOUND and more recursion

Sometimes you have the need to store intermediate results in a temp variable from a query to use for later processing. Here is a simple example of that.

FOUND is a state variable that contains either true or false if the last result returning query returned records or not.

Below is an example that demonstrates, FOUND, storing sql values in temp variables, and more recursion.

This example is the plpgsql equivalent to our SQL Server Transact-SQL article called Using SQL Server 2000's User Defined Function to solve the Tree Problem

Also check out Using PostGreSQL User-Defined Functions to solve the Tree Problem

CREATE TABLE employees(employeeid varchar(50) PRIMARY KEY, managerid varchar(50));
--8.2+ syntax for 8.1 and below need to do individual insert statements
INSERT INTO employees(employeeid, managerid)
VALUES ('Diana', Null), ('Peter', 'Diana'), 
    ('Nancy', 'Peter'), ('John', 'Nancy');

CREATE TYPE reportsTo AS
   (employeeid varchar(50),
    depth integer);


--8.3 syntax
CREATE OR REPLACE FUNCTION fn_reportsTo (param_employeeid varchar(50), param_depth integer)  
RETURNS SETOF reportsTo
 AS  
 $$
 DECLARE
  var_managerid varchar(50);
  var_next_depth integer;
 BEGIN
    var_next_depth := param_depth + 1;
    SELECT managerid INTO var_managerid FROM employees WHERE employeeid = param_employeeid;
    IF FOUND AND var_managerid IS NOT NULL AND var_managerid > '' 
     AND param_depth < 6 AND var_managerid <> param_employeeid THEN
/***We stop if a person is their own manager or  a person has no manager or 
We've  exceeded a depth of 5 (to prevent potential infinite recursion ***/
        RETURN QUERY 
            SELECT employeeid, param_depth
                FROM employees M
                WHERE M.employeeid = param_employeeid
            UNION ALL
                SELECT employeeid, depth FROM fn_reportsTo(var_managerid, var_next_depth);
    ELSE 
        RETURN QUERY SELECT param_employeeid As employeeid, param_depth As depth;
    END IF;
 END;
$$
LANGUAGE 'plpgsql' STABLE;


SELECT *
    FROM fn_reportsTo('John');


 employeeid | depth
------------+-------
 John       |     0
 Nancy      |     1
 Peter      |     2
 Diana      |     3

Overloading Function names

PostgreSQL unlike Microsoft SQL Server, supports function name and argument overloading. Which in a nutshell means you can have multiple functions with the same name as long as they take a different number of arguments and/or different argument types. The functions don't even need to be written in the same language. With that said we can make our function a bit nicer by doing this.

CREATE OR REPLACE FUNCTION fn_reportsTo(param_employeeid varchar(50))
RETURNS SETOF reportsTo
AS
$$
    SELECT * FROM fn_reportsTo($1, 0);
$$
LANGUAGE 'sql' STABLE;

By doing the above we kill 2 birds with one stone:

1. We no longer have to pass in a goofy 0 for depth.
2. We can now use a unique feature of functions written in SQL and C that allows set returning functions in those languages to be used in the SELECT clause as described in our Trojan SQL Function Hack - A PL Lemma in Disguise
   
   Which allows us to do this:
   

   SELECT e.employeeid As emp, (fn_reportsTo(employeeid)).*
       FROM employees E
       ORDER BY e.employeeid, depth;
   
     emp  | employeeid | depth
   -------+------------+-------
    Diana | Diana      |     0
    John  | John       |     0
    John  | Nancy      |     1
    John  | Peter      |     2
    John  | Diana      |     3
    Nancy | Nancy      |     0
    Nancy | Peter      |     1
    Nancy | Diana      |     2
    Peter | Peter      |     0
    Peter | Diana      |     1
   

What is in store in 8.4

8.4 has a couple of things cooking to advance SQL in PostgreSQL and improved features for PL/PGSQL. Below is just a sampling.

1. David Fetters Trees and More http://fetter.org/Trees_and_More_WEST_2008.pdf - which demonstrates how to write recursive SQL statements with upcoming Common Table Expressions in 8.4
2. Hubert's Waiting for 8.4 - pl/* srf functions in selects http://www.depesz.com/index.php/2008/11/03/waiting-for-84-pl-srf-functions-in-selects/ - this one is a pretty important one I think. It means no longer a need for a trojan like hack for 8.4+
3. Hubert's Waiting for 8.4 - RETURNS TABLE http://www.depesz.com/index.php/2008/08/04/waiting-for-84-returns-table/ Again this feature I consider to be pretty important. Especially for people coming from Microsoft SQL Server backgrounds from a comfort stand-point (look at how similar SQL Servers table returning functions look). It also means no longer needing to create a custom type as we did above if you want to return a custom set of fields.