|The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 478 pages
RRP £14.95 ($19.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
18.104.22.168 RETURN NEXT and RETURN QUERY
RETURN NEXT expression; RETURN QUERY query; RETURN QUERY EXECUTE command-string [ USING expression [, ... ] ];
When a PL/pgSQL function is declared to return
SETOF sometype, the procedure
to follow is slightly different. In that case, the individual
items to return are specified by a sequence of
RETURN QUERY commands, and
then a final
RETURN command with no argument
is used to indicate that the function has finished executing.
RETURN NEXT can be used with both scalar and
composite data types; with a composite result type, an entire
“table” of results will be returned.
RETURN QUERY appends the results of executing
a query to the function's result set.
RETURN QUERY can be freely
intermixed in a single set-returning function, in which case
their results will be concatenated.
RETURN NEXT and
QUERY do not actually return from the function--they simply append zero or more rows to the function's result
set. Execution then continues with the next statement in the
PL/pgSQL function. As successive
RETURN NEXT or
QUERY commands are executed, the result set is built
up. A final
RETURN, which should have no
argument, causes control to exit the function (or you can just
let control reach the end of the function).
RETURN QUERY has a variant
RETURN QUERY EXECUTE, which specifies the
query to be executed dynamically. Parameter expressions can
be inserted into the computed query string via
in just the same way as in the
If you declared the function with output parameters, write just
RETURN NEXT with no expression. On each
execution, the current values of the output parameter
variable(s) will be saved for eventual return as a row of the
result. Note that you must declare the function as returning
SETOF record when there are multiple output
when there is just one output parameter of type
sometype, in order to create a set-returning
function with output parameters.
Here is an example of a function using
CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT); INSERT INTO foo VALUES (1, 2, 'three'); INSERT INTO foo VALUES (4, 5, 'six'); CREATE OR REPLACE FUNCTION getAllFoo() RETURNS SETOF foo AS $BODY$ DECLARE r foo%rowtype; BEGIN FOR r IN SELECT * FROM foo WHERE fooid > 0 LOOP -- can do some processing here RETURN NEXT r; -- return current row of SELECT END LOOP; RETURN; END $BODY$ LANGUAGE 'plpgsql' ; SELECT * FROM getallfoo();
Note: The current implementation of
RETURN QUERYstores the entire result set before returning from the function, as discussed above. That means that if a PL/pgSQL function produces a very large result set, performance might be poor: data will be written to disk to avoid memory exhaustion, but the function itself will not return until the entire result set has been generated. A future version of PL/pgSQL might allow users to define set-returning functions that do not have this limitation. Currently, the point at which data begins being written to disk is controlled by the
work_memconfiguration variable. Administrators who have sufficient memory to store larger result sets in memory should consider increasing this parameter.
|ISBN 9781906966065||The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide||See the print edition|