Here's an example:
CREATE OR REPLACE FUNCTION squares(IN ct INTEGER, OUT INTEGER, OUT INTEGER) RETURNS SETOF RECORD AS $$ BEGIN FOR i IN 0..ct-1 LOOP RETURN QUERY SELECT i, POWER(i,2)::INTEGER; END LOOP; END; $$ LANGUAGE 'plpgsql'; SELECT * FROM squares(3);
The third line declares the return type to be SETOF RECORD, indicating that it will return a variable number of elements whose type is RECORD, to be specified elsewhere. The 'elsewhere' is the set of OUT parameters, which indicate that the RECORD is two integers. The OUT variables are shown in line 2. Line 1 names the function, and declares one input parameter to take a count limit.
Lines 6 through 8 define a loop, which iterates over values from 0 to the count limit. Each iteration runs line 7, which we will discuss in the next paragraph. Lines 4,5,9,10 are just boilerplate that is found in every PL/pgSQL function. Line 12 provides a context in which to run the function and display the results.
Get Record from SELECT
Line 7 is:
RETURN QUERY SELECT i, POWER(i,2):INTEGER. The
RETURNtells us this line is part of the results. The
QUERYcatches the results of the subsequent
SELECT, as an ordinary
SELECTstatement is not permitted in PL/pgSQL. The
SELECTs in PL/pgSQL must be qualified somehow, with an
INTOclause or a
QUERYas above. The rest of the line just creates a two part record of the counter index and its square. The
::INTEGERcast avoids a type error; as
POWERis a NUMERIC returning function.
Starting in version 8.4, there is a nice
TABLEsyntax, for returning records and I will discuss that in a future post. The Rdbhost server does not support 8.4 at present, but is scheduled for an upgrade.
If the embedded query returns multiple records, that works too, as all such records get included; witness this variation to the example, which provides both squares and cubes:
BEGIN FOR i IN 0..ct-1 LOOP RETURN QUERY SELECT i, POWER(i,2)::INTEGER UNION SELECT i, POWER(i,3)::INTEGER; END LOOP; END;
An alternate way to return records is with the
RETURN NEXTsyntax. This example names the two output parameters, assigns to them, and then adds their aggregate to the return set.
Line 3 names the output parameters. Lines 8 and 9 put values into those named output parameters. Line 10DROP FUNCTION squares(INTEGER); CREATE OR REPLACE FUNCTION squares(IN ct INTEGER, OUT a INTEGER, OUT b INTEGER) RETURNS SETOF RECORD AS $$ BEGIN FOR i IN 0..ct-1 LOOP a := i; b := POWER(i,2)::INTEGER; RETURN NEXT; END LOOP; END; $$ LANGUAGE 'plpgsql'; SELECT * FROM squares(3);
RETURN NEXTcreates a record from the above parameters, and adds it to the result set. The columns created are named after the parameters, so you might want names longer and more informative than 'a' and 'b'.
Later PostgreSQL versions support more elegant syntax for returning records, but the above patterns do the job, and can still be used in current versions.
That's enough for this week. This week, we looked at getting multiple records out of the function. Next week, we will look at getting multiple rows into the function to work with.