Thursday, January 27, 2011

More elegant forms of returning records.

Like the blog subtitle says, I learn PL/pgSQL as I go, and share the lessons with you.  A couple of weeks ago, I posted on returning a few records using OUT parameters.  I used that method because it is what I knew.  A reader, Tzvi R., kindly commented with a couple of ways to return records more gracefully.  In this post, I am giving those methods more prominence than the comment provided.

SETOF RECORD
Here the record is defined within the function, keeping the parameter list a clean list of inputs.  A limitation of this approach is that since the record definition is hidden from the caller, the column list must be provided as part of the retrieving SELECT.
CREATE OR REPLACE FUNCTION squares(ct INT)
   RETURNS SETOF RECORD
AS $$
DECLARE
  v_rec RECORD;
BEGIN
  FOR i IN 0..ct-1 LOOP
    SELECT i, POWER(i,2)::INT INTO v_rec;
    RETURN NEXT v_rec;
  END LOOP;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE;

SELECT * FROM squares(5) AS (A INT, B INT);

Line 5 creates a variable, of indeterminate shape, for the output record.  Line 8 creates the record using a SELECT .. INTO, and inserts it into the variable, giving the variable its shape.

Line 9 is a RETURN NEXT.., which adds the record to the list of records to eventually return.  This form of RETURN does not terminate the function, but rather augments the return value, which gets returned whenever the function returns.  In this example, we just passively fall out the bottom of the function, but a bare RETURN can be used to explicitly exit the function.  The bare RETURN does not interfere with the returning of the built-up result set.

Predefined Types
If you define the record type outside of the function, and use it in the function definition, the caller no longer needs a column list, but determines columns from the record type.
CREATE TYPE square_type AS (a INT, b INT);

CREATE OR REPLACE FUNCTION squares(ct INT)
   RETURNS SETOF square_type
AS $$
DECLARE
  v_rec square_type;
BEGIN
  FOR i IN 0..ct-1 LOOP
    SELECT i, POWER(i,2)::INT INTO v_rec;
    RETURN NEXT v_rec;
  END LOOP;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE;

SELECT * FROM squares(5);

Line 1 is the type definition, defining 'square_type' as a pair of INT fields.  Lines 4 and 7 define the return type and the variable type as 'square_type'.  Because the caller can see the return type of the function from the function definition, it knows to treat the return values as pairs of INT columns, and Line 17 can be appealingly spare.

In my opinion, this is the most elegant of the three forms; it does require an additional line to define the type, and an additional addition to the namespace, but it reads easily.  The next best is the OUT parameter method. Having to specify the row shape in the calling SELECT, as in the first example above, is just too awkward.

One caution is that there is no CREATE OR REPLACE TYPE <typename>, so you need to use a DROP TYPE <typename> if you wish to repeat the execution of the above quoted code block complete.   In another week or two, I plan to show how to use Exception handling to gracefully absorb the errors produced by redundantly creating a type.

Thank you, Tzvi, for your assistance.

2 Feb changed function types to IMMUTABLE

6 comments:

  1. Couldn't those be defined as IMMUTABLE?

    ReplyDelete
  2. Even cleaner: use RETURNS TABLEand RETURN QUERY:

    CREATE OR REPLACE FUNCTION squares(
        ct INT
    ) RETURNS TABLE (
        a int,
        b int
    ) LANGUAGE plpgsql AS $$
    BEGIN
        RETURN QUERY SELECT i, POWER(i,2)::INT
            FROM generate_series(0, ct-1) AS i;
    END;
    $$;

    Of course, something this simple doesn't have to be written in PL/pgSQL; it can be just straight SQL:

    CREATE OR REPLACE FUNCTION squares(
        ct INT
    ) RETURNS TABLE (
        a int,
        b int
    ) LANGUAGE sql AS $$
        SELECT i, POWER(i,2)::INT
          FROM generate_series(0, $1-1) AS i;
    $$;

    PS: OMG Blogger comment formatting is awful. No <code>! No <pre>!

    ReplyDelete
  3. JB:
    Yes, they could be IMMUTABLE. I will edit that into the post tomorrow.

    Theory:
    I believe the RETURNS TABLE syntax entered PostgreSQL in 8.4; my test installation is still 8.3.

    Thanks to both of you.

    ReplyDelete
  4. The "problem" with using types as output definitions, is that it is not so easy to maintain the code later.

    As types are not extendable/alterable yet, it can be quite a headache to rewrite your stored procedure so, that it returns another field in the resulting type. With out parameters, you can just add a new out parameter and DROP/CREATE your function. With returning type, you have to drop function, then drop type, create your type and create function. The horror starts, when 2 functions are using the same type. So you have to drop all the type dependences first, to alter it, or create a new type, that will have probably some suffix with a number... so the amount of garbage that you will create in the database will be increasing with unclear dependencies...

    So my suggestion would be not to use result types.

    ReplyDelete