From b2b9b4d59c6bccea65f33c17345bcd3296eca1be Mon Sep 17 00:00:00 2001 From: Neil Conway Date: Wed, 25 Jul 2007 04:19:09 +0000 Subject: Implement RETURN QUERY for PL/PgSQL. This provides some convenient syntax sugar for PL/PgSQL set-returning functions that want to return the result of evaluating a query; it should also be more efficient than repeated RETURN NEXT statements. Based on an earlier patch from Pavel Stehule. --- doc/src/sgml/plpgsql.sgml | 77 +++++++++++++++++++++++++++++------------------ 1 file changed, 48 insertions(+), 29 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 24bcf52a4f7..70a0c4e6dcc 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1,4 +1,4 @@ - + <application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language @@ -135,7 +135,9 @@ PL/pgSQL functions can also be declared to return a set, or table, of any data type they can return a single instance of. Such a function generates its output by executing - RETURN NEXT for each desired element of the result set. + RETURN NEXT for each desired element of the result + set, or by using RETURN QUERY to output the result of + evaluating a query. @@ -1349,52 +1351,69 @@ RETURN expression; - <command>RETURN NEXT</> + <command>RETURN NEXT</> and <command>RETURN QUERY</command> + + RETURN NEXT + in PL/PgSQL + + + RETURN QUERY + in PL/PgSQL + RETURN NEXT expression; +RETURN QUERY query; 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 in RETURN NEXT - 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. + items to return are specified by a sequence of RETURN + NEXT or 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 + NEXT and RETURN QUERY can be freely + intermixed in a single set-returning function, in which case + their results will be concatenated. - RETURN NEXT does not actually return from the - function — it simply saves away the value of the expression. - Execution then continues with the next statement in - the PL/pgSQL function. As successive - RETURN NEXT 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 NEXT and RETURN + 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 RETURN + 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). 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 parameters, or - SETOF sometype when there is - just one output parameter of type sometype, in - order to create a set-returning function with output parameters. + 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 + parameters, or SETOF sometype + when there is just one output parameter of type + sometype, in order to create a set-returning + function with output parameters. - Functions that use RETURN NEXT should be - called in the following fashion: + Functions that use RETURN NEXT or + RETURN QUERY should be called in the + following fashion: SELECT * FROM some_func(); @@ -1407,7 +1426,7 @@ SELECT * FROM some_func(); The current implementation of RETURN NEXT - for PL/pgSQL stores the entire result set + and RETURN QUERY stores 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 -- cgit v1.2.3