In a prior article Use of Out and InOut Parameters we demonstrated how to use OUT parameters and INOUT parameters to return a set of records from a PostgreSQL function. There is another approach to doing this, and that is to use the ANSI Standard RETURNS TABLE construct. If you come from a SQL Server or IBM DB2 background, the RETURNS TABLE construct is probably most familiar, but still how you use it and what is legal in it is a little different than it is in SQL Server or IBM DB2. We'll save the contrast compare as a topic for another article.
In terms of performance between using OUT vs. RETURNS TABLE, we haven't noticed much of a difference. The main thing that is nice about RETURNS TABLE is just that it's syntactically more pleasing in the sense that its clearer the structure of what you are returning.
In these next examples, we'll demonstrate similar examples we showed in the aforementioned article except using the
RETURNS TABLE.
Be warned that the RETURNS TABLE construct is only available for PostgreSQL 8.4+, while the OUT approach
has existed since PostgreSQL 8.1. With that said, if you need your code to work on 8.3 or lower, you can't use RETURNS TABLE.
When in doubt about a feature and you are creating code that needs to support earlier versions of PostgreSQL
(as we have to in the PostGIS development group),
or you want to get stubborn users off old antiquated versions of PostgreSQL and need a bit of ammunition
(as we have to (on PostGIS development including our own developers - and you know who you are :) ) )
check the
PostgreSQL feature matrix.
It will save you a lot of grief.
--returning a single record using SQL function
CREATE OR REPLACE FUNCTION fn_sqltestout(param_subject text, pos integer)
RETURNS TABLE(subject_scramble text, subject_char text)
AS
$$
SELECT substring($1, 1,CAST(random()*length($1) As integer)) ,
substring($1, 1,1) As subject_char;
$$
LANGUAGE 'sql' VOLATILE;
-- example use
SELECT (fn_sqltestout('This is a test subject')).subject_scramble;
SELECT subject_scramble, subject_char FROM fn_sqltestout('This is a test subject');
--Same function but written in plpgsql
--PLPGSQL example -- return one record
CREATE OR REPLACE FUNCTION fn_plpgsqltestout(param_subject text)
RETURNS TABLE(subject_scramble text, subject_char text)
AS
$$
BEGIN
subject_scramble := substring($1, 1,CAST(random()*length($1) As integer));
subject_char := substring($1, 1,1);
RETURN NEXT;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;
-- example use
SELECT (fn_sqltestout('This is a test subject')).subject_scramble;
SELECT subject_scramble, subject_char FROM fn_sqltestout('This is a test subject');
-- test data to use --
CREATE TABLE testtable(id integer PRIMARY KEY, test text);
INSERT INTO testtable(id,test)
VALUES (1, 'Potato'), (2, 'Potato'), (3, 'Cheese'), (4, 'Cheese Dog');
--SQL function returning multiple records
CREATE OR REPLACE FUNCTION fn_sqltestmulti(param_subject varchar)
RETURNS TABLE(test_id integer, test_stuff text)
AS
$$
SELECT id, test
FROM testtable WHERE test LIKE $1;
$$
LANGUAGE 'sql' VOLATILE;
-- example use
SELECT (fn_sqltestmulti('Cheese%')).test_stuff;
SELECT test_stuff FROM fn_sqltestmulti('Cheese%');
--both return:
test_stuff
------------
Cheese
Cheese Dog
-- plpgsql function returning multiple records
-- note RETURN QUERY was introduced in 8.3
-- variant 1
CREATE OR REPLACE FUNCTION fn_plpgsqltestmulti(param_subject varchar)
RETURNS TABLE(test_id integer, test_stuff text)
AS
$$
BEGIN
RETURN QUERY SELECT id, test
FROM testtable WHERE test LIKE param_subject;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;
-- variant 2 use this if you need to do something additional
-- or conditionally return values or more dynamic stuff
-- RETURN QUERY is generally more succinct and faster
CREATE OR REPLACE FUNCTION fn_plpgsqltestmulti(param_subject varchar)
RETURNS TABLE(test_id integer, test_stuff text)
AS
$$
DECLARE
var_r record;
BEGIN
FOR var_r IN(SELECT id, test
FROM test WHERE test LIKE param_subject) LOOP
test_id := var_r.id ; test_stuff := var_r.test;
RETURN NEXT;
END LOOP;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;
-- example use
-- This is legal in PostgreSQL 8.4+
-- (prior versions plpgsql could not be called this way)
SELECT (fn_plpgsqltestmulti('Cheese%')).test_stuff;
SELECT test_stuff FROM fn_plpgsqltestmulti('Cheese%');
-- All return:
test_stuff
------------
Cheese
Cheese Dog
It's great that there are so many ways you can choose to hang yourself, but which way is the least painful and the quickest? I was wondering that myself so I did some tests and the tests seemed to suggest there is no difference in speed, however it suggested something else I really wasn't expecting (about 9.0) that what I thought was really bad form for speed (though shorter) becomes faster or a non-issue when you move to 9.0
CREATE OR REPLACE FUNCTION testspeed_table(it numeric(20))
RETURNS TABLE(newit numeric(20), itprod numeric(20))
AS
$$
SELECT j::numeric(20), $1*j::numeric(20) As itprod
FROM generate_series(1,$1::bigint) As j;
$$
LANGUAGE 'sql' VOLATILE;
CREATE OR REPLACE FUNCTION testspeed_out(it numeric(20),
OUT newit numeric(20), OUT itprod numeric(20) )
RETURNS setof record
AS
$$
SELECT j::numeric(20), $1*j::numeric(20) As itprod
FROM generate_series(1,$1::bigint) As j;
$$
LANGUAGE 'sql' VOLATILE;
The results suggest that what I thought was the case as described in Output parameters, custom data type gotchas may not always be the case or at least not for SQL written functions. More analysis is called for. Also notice how the speeds flips between 8.4 and 9.0 (or at least on Windows), where calling the ().* was worse in 8.4 and in 9.0 actually faster than the FROM syntax. This is something I'll need to investigate with plpgsql functions with real tests and on Linux platform as well to make sure its not abberations in my development environment or idiosyncracies of the test example. I would be interested in knowing the explanations of others regarding what we consider a bit surprising of results and under what conditions this manifests.
-- On a 9.0.2 Windows 32-bit
-- 1813 ms, 1750 ms
SELECT (testspeed_table(300000)).*;
-- 2453 ms, 2234ms, 2031ms
SELECT * FROM
testspeed_table(300000);
-- 1781ms, 1750ms
SELECT (testspeed_out(300000)).*;
-- 2766ms, 2015ms
SELECT *
FROM testspeed_out(300000);
-- On a 8.4 Windows 32-bit
-- 3062 ms, 3047 ms
SELECT (testspeed_table(300000)).*;
-- 2719ms, 2234ms, 2218ms
SELECT * FROM
testspeed_table(300000);
-- 3078 ms, 3078 ms, 3063 ms
SELECT (testspeed_out(300000)).*;
-- 2922 ms, 2860 ms,
SELECT *
FROM testspeed_out(300000);