set returning functions in 8.3 – select * from depesz;

just couple of days ago i read about a new, great addition to postgresql 8.3 – “return query" in pl/pgsql.

what does it do?

in set returning functions, when you wanted to return multiple rows from a given query you had to:

FOR record IN SELECT ..... LOOP
    RETURN NEXT record;
END LOOP;

now, you can simply:

RETURN QUERY SELECT ...;

what's more – since RETURN QUERY doesn't terminate function (just like return next) you can:

RETURN QUERY SELECT something;

RETURN QUERY SELECT something ELSE;

and then you'll get (more or less) “union all" of the queries.

additionally – return query is supposed to be faster then return next/loop.

so, let's test it.

i got brand new cvs-head-based pg, compiled and ran. then i created test-set:

CREATE TABLE test AS SELECT i FROM generate_series(1,1000) i;
CREATE OR REPLACE FUNCTION return_next_simple() RETURNS SETOF test AS $BODY$
DECLARE
    temprec test%rowtype;
BEGIN
    FOR temprec IN SELECT * FROM test LOOP
        RETURN NEXT temprec;
    END loop;
    RETURN;
END;
$BODY$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION return_query_simple() RETURNS SETOF test AS $BODY$
DECLARE
    temprec test%rowtype;
BEGIN
    RETURN query SELECT * FROM test;
    RETURN;
END;
$BODY$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION return_next_simple_ordered() RETURNS SETOF test AS $BODY$
DECLARE
    temprec test%rowtype;
BEGIN
    FOR temprec IN SELECT * FROM test ORDER BY i DESC LOOP
        RETURN NEXT temprec;
    END loop;
    RETURN;
END;
$BODY$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION return_query_simple_ordered() RETURNS SETOF test AS $BODY$
DECLARE
    temprec test%rowtype;
BEGIN
    RETURN query SELECT * FROM test ORDER BY i DESC;
    RETURN;
END;
$BODY$ LANGUAGE plpgsql;
CREATE TYPE test_complex AS ( "Schema" name, "Name" name, "Type" TEXT, "Owner" name );
CREATE OR REPLACE FUNCTION return_next_complex() RETURNS SETOF test_complex AS $BODY$
DECLARE
    temprec test_complex;
BEGIN
    FOR temprec IN SELECT n.nspname AS "Schema",
      c.relname AS "Name",
      CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END AS "Type",
      r.rolname AS "Owner"
    FROM pg_catalog.pg_class c
         JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
         LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relkind IN ('r','v','S','')
      AND pg_catalog.pg_table_is_visible(c.oid)
    ORDER BY 1,2
    LOOP
        RETURN NEXT temprec;
    END loop;
    RETURN;
END;
$BODY$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION return_query_complex() RETURNS SETOF test_complex AS $BODY$
DECLARE
    temprec test_complex;
BEGIN
    RETURN query SELECT n.nspname AS "Schema",
                   c.relname AS "Name",
                   CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END AS "Type",
                   r.rolname AS "Owner"
                 FROM pg_catalog.pg_class c
                      JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
                      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
                 WHERE c.relkind IN ('r','v','S','')
                   AND pg_catalog.pg_table_is_visible(c.oid)
                 ORDER BY 1,2;
    RETURN;
END;
$BODY$ LANGUAGE plpgsql;

explanation:

new table for simple test – select * from table, or select * from table order by field
custom type for returning recordsets with more then 1 field per set

i have chosen queries that were fast – to make it possible to check how much burden does return next/return query really do.

then i wrote small perl program which did the test.

in this test i ran several hundred thousands of queries to see which way is faster.

each type of query (select * from table; select * from table order by field; complex-query-with-joins) was executed in 4 different modes:

simple select …
execute of prepared plan with given select (this plan was prepared only once)
select * from function() where function used loop/return next approach
select * from function() where function used return query approach

results:

test:	min	max	sum	iter/s
*Test on simplest query: select from test**
next	0.00390	0.04018	408.59155	244.74319
query	0.00350	0.03075	365.67770	273.46486
sql-prep	0.00334	0.03152	350.42446	285.36821
sql	0.00318	0.06028	334.28738	299.14380

test:	min	max	sum	iter/s
*Test on ordered query: select from test order by i desc**
next	0.00458	0.03273	480.10789	208.28652
query	0.00419	0.03174	441.19612	226.65657
sql-prep	0.00402	0.03105	422.16855	236.87221
sql	0.00390	0.03116	411.16180	243.21326

test:	min	max	sum	iter/s
Test on complex query: (\d with all schemas, 70 entries returned)
sql	0.00264	0.03053	2857.79664	349.91993
next	0.00226	0.03032	2413.23163	414.38210
query	0.00215	0.05947	2290.68159	436.55129
sql-prep	0.00210	0.06050	2275.77135	439.41146

results quite surprised me.

first of all – return next is not that slow?! actually – it is faster to use return next then plain old sql in case we use complex queries (put in here your definition of complex query – you can see tested queries in mentioned perl program).

then. return query was faster. how much faster – between 5 and 12%. this, plus the fact that code is more readable makes it a great addition to plpgsql.

what was really surprising is that when dealing with simples possible queries (single table, no where) pumping sql's is the fastest way. it's even faster then using prepare/execute?! (remember: prepare was only called once!)

3 thoughts on “set returning functions in 8.3”

Alejandro says:

2007-10-23 at 08:15

Great Page!
Really clear on the subject also very informative.
Pingback: select * from depesz;» Blog Archive » Waiting for 8.4 - RETURN QUERY EXECUTE and cursor_tuple_fraction
Pingback: Postgres Return Query bug | Npgsql Blog

Comments are closed.