Returning a few rows

This week, we will look at returning a few rows from a function. Database programming typically involves getting sets of records, so this output ability is natural to look for, in a programming language that runs internally to a database engine.

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);

Record Type
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 RETURN tells us this line is part of the results.   The QUERY catches the results of the subsequent SELECT, as an ordinary SELECT statement is not permitted in PL/pgSQL.  The SELECTs in PL/pgSQL must be qualified somehow, with an INTO clause or a QUERY as above.  The rest of the line just creates a two part record of the counter index and its square.  The ::INTEGER cast avoids a type error; as POWER is a NUMERIC returning function.

Starting in version 8.4, there is a nice TABLE syntax, 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.

Multiple Records
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;

RETURN NEXT
An alternate way to return records is with the RETURN NEXT syntax.  This example names the two output parameters, assigns to them, and then adds their aggregate to the return set.

DROP 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);

Line 3 names the output parameters.  Lines 8 and 9 put values into those named output parameters.  Line 10 RETURN NEXT creates 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.