Quick Guide to writing PLPGSQL Functions: Part 1   👁 Checkout our PostGIS in Action book 2nd Edition. First chapter is a free download

In this series we'll go over writing PLPGSQL stored functions. We shall follow up in a later issue with a one page cheat sheet.

The Anatomy of a PLPGSQL FUNCTION

All PLPGSQL functions follow a structure that looks something like the below.

CREATE OR REPLACE FUNCTION fnsomefunc(numtimes integer, msg text)
 RETURNS text AS
$$
DECLARE
 strresult text;
BEGIN
 strresult := '';
 IF numtimes > 0 THEN
 FOR i IN 1 .. numtimes LOOP
 strresult := strresult || msg || E'\r\n';
 END LOOP;
 END IF;
 RETURN strresult;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE
SECURITY DEFINER
 COST 10;

--Tocallthefunctionwedothisanditreturnstenhellothere'swith
carriage returns as a single text field.
SELECT fnsomefunc(10, 'Hellothere');

The basic make-up of a PLPGSQL function is as follows:

1. There is the function interface that defines the args and the return type
2. There is the body which in modern versions of PostgreSQL (8+) the preferred encapsulation is dollar quoting vs. using a single quote
3. Within the body: There is a declaration of variables section which is optional
4. Then there is a BEGIN END structure that defines the meat of the function. Unlike sql functions which currently require you to refer to variables by their ordinal position $1, $2, $3 etc. in PLPGSQL you can refer to variables by there name.
5. After the body, like all PostgreSQL functions, is noted the Language and a tag that denotes how it should be cached. In this case we have noted IMMUTABLE meaning that the output of the function can be expected to be the same if the inputs are the same. Other options are STABLE - meaning it will not change within a query given same inputs and VOLATILE such as functions involving random() and CURRENT_TIMESTAMP that can be expected to change output even in the same query call.
6. PostgreSQL 8.3 introduced the ability to set costs and estimated rows returned for a function. For a scalar function the rows is not applicable so we leave that out for this simple example. The cost is relative to other functions and defaults to 100 unless you change it. Nuances of COST and caveats are outlined in our New Features for PostgreSQL Stored Functions

7. Note also the clause after the caching model is sometimes the words SECURITY DEFINER which means the function is run under the context of the owner of the function. This means the function can do anything the owner of the function has security to do even if the person running the function does not have those rights. This portion applies not just to PLPGSQL functions but any. If this clause is left out, then a function runs under the security context of the person running the function.

   For users coming from SQL Server - this is similar in concept to SQL Server 2005 - EXECUTE AS OWNER (leaving Security definer out is equivalent to EXECUTE As CALLER in sql server). Note SQL Server 2005 has an additional option called EXECUTE As 'user_name' which PostgreSQL lacks that allows you to run under a named user that need not be the owner of the function.

   For MySQL users, SECURITY DEFINER exists as well and works more or less the same as it does in PostgreSQL.

8. Pretty much all the functions you can write in PostgreSQL whether SQL, PLPGSQL or some other language can use recursion. We'll go over an example of that in another part of this series.

Conditional Logic

PLPGSQL has a couple of conditional logic structures. In the above we saw the simple IF THEN. There also exists IF .. ELSIF ..ELSIF END IF, IF ..ELSE ..END IF. We shall demonstrate by making dumb changes to our above.

CREATE OR REPLACE FUNCTION fnsomefunc(numtimes integer, msg text)
 RETURNS text AS
$$
DECLARE
 strresult text;
BEGIN
 strresult := '';
 IF numtimes = 42 THEN
 strresult := 'Rightyouare!';
 ELSIF numtimes > 0 AND numtimes < 100 THEN
 FOR i IN 1 .. numtimes LOOP
 strresult := strresult || msg || E'\r\n';
 END LOOP;
 ELSE
 strresult := 'Youcannotdothat.Pleasedon''tabuseourgenerosity.';
 IF numtimes <= 0 THEN
 strresult := strresult || 'Youareabozo.';
 ELSIF numtimes > 1000 THEN
 strresult := strresult || 'Idonotknowwhoyouthinkyouare.
Youarewayoutofcontrol.';
 END IF;
 END IF;
 RETURN strresult;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE;

SELECT fnsomefunc(42, 'Hellothere'); 
SELECT fnsomefunc(200, 'Hellothere'); 
SELECT fnsomefunc(5000, 'Hellothere'); 

Control Flow

In the above example we saw a variant of the FOR LOOP - below are a listing of the other basic control structures. In part 2 we shall delve into using some of these.

The basic control flow structures available in PLPGSQL are:

• FOR somevariable IN (1 ...someendnumber) LOOP .. END LOOP;
• FOR somevariable IN REVERSE someendnumber .. 1 BY somestep LOOP .. END LOOP;
• FOR somevariable IN (somesqlquery) LOOP ..RETURN NEXT; .. END LOOP;
• LOOP ..logic statements EXIT .. EXIT WHEN .. CONTINUE WHEN .. END LOOP;
• WHILE ... LOOP ... END LOOP;
• EXCEPTION WHEN .... WHEN ..
• Introduced in 8.3 RETURN QUERY which can be in any LOOP like structure or stand alone