Quick Guide to writing PLPGSQL Functions: Part 2

In first part Guide to Writing PLPGSQL functions, we covered the plpgsql function anatomy and basic IF and FOR loops. In this second part of our PLPGSQL Quick Guide series, we shall delve more into control flow. As we mentioned in the previous part, the following control flow constructs exist for PLPGSQL.

FOR somevariable IN (1 ...someendnumber) LOOP .. END LOOP;
FOR somevariable IN REVERSE someendnumber .. 1 BY somestep LOOP .. END LOOP;
FOR somevariable IN EXECUTE(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. This is covered in New Features of PostgreSQL Functions

In this section we shall demonstrate looping thru sets of records and writing a set returning function. In the next section after, we shall delve a little into recursive functions, doing table updates, and raising notices.

The FOR somevariable IN somesqlquery

This in pre-8.3 was the most common construct for looping thru records. It is still the only way to return a set of records where the query statement is dynamically changing within the procedure. Below is an example of such a thing.


--Just returning a string
CREATE OR REPLACE FUNCTION somefuntext(param_numcount integer)
  RETURNS text AS
$$
DECLARE
    result text := '';
    searchsql text := '';
    var_match text := '';
BEGIN
    searchsql := 'SELECT n || '' down'' As countdown 
                FROM generate_series(' || CAST(param_numcount As text) || ', 1, -1) As n ';
                
    
    FOR var_match IN EXECUTE(searchsql) LOOP
        IF result > '' THEN
            result := result || ';' || var_match;
        ELSE
            result := var_match;
        END IF;
    END LOOP;
    RETURN result;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE;

--To use you would do this --
SELECT somefuntext(10);


---RESULT---
10 down;9 down;8 down;7 down;6 down;5 down;4 down;3 down;2 down;1 down



--Returning a set of strings
CREATE OR REPLACE FUNCTION somefun_settext(param_numcount integer)
  RETURNS SETOF text AS
$$
DECLARE
    result text := '';
    searchsql text := '';
    var_match text := '';
BEGIN
    searchsql := 'SELECT n || '' down'' As countdown 
                FROM generate_series(' || CAST(param_numcount As text) || ', 1, -1) As n ';
                
    
    FOR var_match IN EXECUTE(searchsql) LOOP
        RETURN NEXT var_match;
    END LOOP;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE;

--To use you would do this
SELECT n 
FROM somefuntext(10) As n;


---RESULT---
    n
---------
 10 down
 9 down
 8 down
 7 down
 6 down
 5 down
 4 down
 3 down
 2 down
 1 down
(10 rows)


--Returning a set of anonymous records
CREATE OR REPLACE FUNCTION somefun_recordset(param_numcount integer)
  RETURNS SETOF record AS
$$
DECLARE
    result text := '';
    searchsql text := '';
    var_match record;
BEGIN
    searchsql := 'SELECT n || '' down'' As countdown, n as integer 
                FROM generate_series(' || CAST(param_numcount As text) || ', 1, -1) As n ';
                
    
    FOR var_match IN EXECUTE(searchsql) LOOP
        RETURN NEXT var_match;
    END LOOP;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE;

To call the above, you would do something like below. Note when calling an alias record set, we need to define the structure of the output. The benefit of an alias recordset is that you can have all sorts of queries use the same function as long as your calling query knows the expected structure. The downside is that you have to note the expected structure which quickly becomes annoying if it is always the same.


SELECT r.n , r.countdown
FROM somefun_recordset(10) 
    As r(countdown text, n integer)
    ORDER BY r.n;


---RESULT --
 n  | countdown
----+-----------
  1 | 1 down
  2 | 2 down
  3 | 3 down
  4 | 4 down
  5 | 5 down
  6 | 6 down
  7 | 7 down
  8 | 8 down
  9 | 9 down
 10 | 10 down

In order to not have to specify the output structure of your query, you need to either use a named type or use a table as a type. NOTE: All tables and views in PostgreSQL have a corresponding row type that is autocreated for them so if your output results match a table type, you can use that table record type as output. Below we demonstrate creating a type to hold each row and using it.


CREATE TYPE somefun_type As (countdown text, n integer, somethingrandom numeric);
CREATE OR REPLACE FUNCTION somefun_setoftype(param_numcount integer)
  RETURNS SETOF somefun_type AS
$$
DECLARE
    result text := '';
    searchsql text := '';
    var_match somefun_type;
BEGIN
    searchsql := 'SELECT n || '' down'' As countdown, n as integer, random() As numeric 
                FROM generate_series(' || CAST(param_numcount As text) || ', 1, -1) As n ';
                
    
    FOR var_match IN EXECUTE(searchsql) LOOP
        RETURN NEXT var_match;
    END LOOP;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;


--Example run --
SELECT myfun.n, myfun.somethingrandom 
FROM somefun_setoftype(10) As myfun
WHERE myfun.n IN(2,3);


 n |  somethingrandom
---+-------------------
 3 |  0.78656436316669
 2 | 0.818326753564179