In this third part of our PLPGSQL Quick Guide series, we shall delve into writing recursive functions. Before we do that, we shall demonstrate a very important but trivial feature in PostgreSQL and that is the RAISE NOTICE feature. There are more elegant ways of debugging, but this is the simple brain dead way of doing so.
RAISE Notices in plpgsql are generally used for two reasons:
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.
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
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:
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
8.4 has a couple of things cooking to advance SQL in PostgreSQL and improved features for PL/PGSQL. Below is just a sampling.