Quick Guide to writing PLPGSQL Functions: Part 1

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;

--To call the function we do this and it returns ten hello there's with 
carriage returns as a single text field.
SELECT fnsomefunc(10, 'Hello there');

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 := 'Right you are!';
    ELSIF numtimes > 0 AND numtimes < 100 THEN
        FOR i IN 1 .. numtimes LOOP
            strresult := strresult || msg || E'\r\n';
        END LOOP;
    ELSE
        strresult := 'You can not do that. Please don''t abuse our generosity.';
        IF numtimes <= 0 THEN
            strresult := strresult || ' You are a bozo.';
        ELSIF numtimes > 1000 THEN
            strresult := strresult || ' I do not know who you think you are.  
                    You are way out of control.';
        END IF;
    END IF;
    RETURN strresult;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE;

SELECT fnsomefunc(42, 'Hello there'); 
SELECT fnsomefunc(200, 'Hello there'); 
SELECT fnsomefunc(5000, 'Hello there');

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: