Attached is a patch implementing RETURN QUERY, per earlier discussion, and based on a patch from Pavel Stehule. Like RETURN NEXT, RETURN QUERY doesn't immediately return from the function, allowing RETURN NEXT and RETURN QUERY to be intermixed in a single function.
Barring any objections, I'll apply this tomorrow. -Neil
Index: doc/src/sgml/plpgsql.sgml =================================================================== RCS file: /home/neilc/postgres/cvs_root/pgsql/doc/src/sgml/plpgsql.sgml,v retrieving revision 1.115 diff -p -c -r1.115 plpgsql.sgml *** doc/src/sgml/plpgsql.sgml 16 Jul 2007 17:01:10 -0000 1.115 --- doc/src/sgml/plpgsql.sgml 24 Jul 2007 06:40:47 -0000 *************** *** 135,141 **** <application>PL/pgSQL</> functions can also be declared to return a <quote>set</>, or table, of any data type they can return a single instance of. Such a function generates its output by executing ! <literal>RETURN NEXT</> for each desired element of the result set. </para> <para> --- 135,143 ---- <application>PL/pgSQL</> functions can also be declared to return a <quote>set</>, or table, of any data type they can return a single instance of. Such a function generates its output by executing ! <command>RETURN NEXT</> for each desired element of the result ! set, or by using <command>RETURN QUERY</> to output the result of ! executing a query. </para> <para> *************** RETURN <replaceable>expression</replacea *** 1349,1400 **** </sect3> <sect3> ! <title><command>RETURN NEXT</></title> <synopsis> RETURN NEXT <replaceable>expression</replaceable>; </synopsis> <para> When a <application>PL/pgSQL</> function is declared to return <literal>SETOF <replaceable>sometype</></literal>, the procedure to follow is slightly different. In that case, the individual ! items to return are specified in <command>RETURN NEXT</command> ! commands, and then a final <command>RETURN</command> command ! with no argument is used to indicate that the function has ! finished executing. <command>RETURN NEXT</command> can be used ! with both scalar and composite data types; with a composite result ! type, an entire <quote>table</quote> of results will be returned. </para> <para> ! <command>RETURN NEXT</command> 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 <application>PL/pgSQL</> function. As successive ! <command>RETURN NEXT</command> commands are executed, the result ! set is built up. A final <command>RETURN</command>, which should ! have no argument, causes control to exit the function (or you can ! just let control reach the end of the function). </para> <para> If you declared the function with output parameters, write just <command>RETURN NEXT</command> 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 ! <literal>SETOF record</literal> when there are ! multiple output parameters, or ! <literal>SETOF <replaceable>sometype</></literal> when there is ! just one output parameter of type <replaceable>sometype</>, in ! order to create a set-returning function with output parameters. </para> <para> ! Functions that use <command>RETURN NEXT</command> should be ! called in the following fashion: <programlisting> SELECT * FROM some_func(); --- 1351,1419 ---- </sect3> <sect3> ! <title><command>RETURN NEXT</> and <command>RETURN QUERY</command></title> ! <indexterm> ! <primary>RETURN NEXT</primary> ! <secondary>in PL/PgSQL</secondary> ! </indexterm> ! <indexterm> ! <primary>RETURN QUERY</primary> ! <secondary>in PL/PgSQL</secondary> ! </indexterm> <synopsis> RETURN NEXT <replaceable>expression</replaceable>; + RETURN QUERY <replaceable>query</replaceable>; </synopsis> <para> When a <application>PL/pgSQL</> function is declared to return <literal>SETOF <replaceable>sometype</></literal>, the procedure to follow is slightly different. In that case, the individual ! items to return are specified by a sequence of <command>RETURN ! NEXT</command> or <command>RETURN QUERY</command> commands, and ! then a final <command>RETURN</command> command with no argument ! is used to indicate that the function has finished executing. ! <command>RETURN NEXT</command> can be used with both scalar and ! composite data types; with a composite result type, an entire ! <quote>table</quote> of results will be returned. ! <command>RETURN QUERY</command> appends the results of executing ! a query to the function's result set. <command>RETURN ! NEXT</command> and <command>RETURN QUERY</command> can be freely ! intermixed in a single set-returning function, in which case ! their results will be concatenated. </para> <para> ! <command>RETURN NEXT</command> and <command>RETURN ! QUERY</command> 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 ! <application>PL/pgSQL</> function. As successive ! <command>RETURN NEXT</command> or <command>RETURN ! QUERY</command> commands are executed, the result set is built ! up. A final <command>RETURN</command>, which should have no ! argument, causes control to exit the function (or you can just ! let control reach the end of the function). </para> <para> If you declared the function with output parameters, write just <command>RETURN NEXT</command> 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 ! <literal>SETOF record</literal> when there are multiple output ! parameters, or <literal>SETOF <replaceable>sometype</></literal> ! when there is just one output parameter of type ! <replaceable>sometype</>, in order to create a set-returning ! function with output parameters. </para> <para> ! Functions that use <command>RETURN NEXT</command> or ! <command>RETURN QUERY</command> should be called in the ! following fashion: <programlisting> SELECT * FROM some_func(); *************** SELECT * FROM some_func(); *** 1407,1412 **** --- 1426,1432 ---- <note> <para> The current implementation of <command>RETURN NEXT</command> + and <command>RETURN QUERY</command> for <application>PL/pgSQL</> stores the entire result set before returning from the function, as discussed above. That means that if a <application>PL/pgSQL</> function produces a Index: src/pl/plpgsql/src/gram.y =================================================================== RCS file: /home/neilc/postgres/cvs_root/pgsql/src/pl/plpgsql/src/gram.y,v retrieving revision 1.104 diff -p -c -r1.104 gram.y *** src/pl/plpgsql/src/gram.y 16 Jul 2007 17:01:10 -0000 1.104 --- src/pl/plpgsql/src/gram.y 24 Jul 2007 06:43:32 -0000 *************** static PLpgSQL_stmt *make_execsql_stmt(c *** 32,37 **** --- 32,38 ---- static PLpgSQL_stmt_fetch *read_fetch_direction(void); static PLpgSQL_stmt *make_return_stmt(int lineno); static PLpgSQL_stmt *make_return_next_stmt(int lineno); + static PLpgSQL_stmt *make_return_query_stmt(int lineno); static void check_assignable(PLpgSQL_datum *datum); static void read_into_target(PLpgSQL_rec **rec, PLpgSQL_row **row, bool *strict); *************** static void check_labels(const char * *** 187,192 **** --- 188,194 ---- %token K_NULL %token K_OPEN %token K_OR + %token K_QUERY %token K_PERFORM %token K_ROW_COUNT %token K_RAISE *************** stmt_return : K_RETURN lno *** 1171,1176 **** --- 1173,1182 ---- { $$ = make_return_next_stmt($2); } + else if (tok == K_QUERY) + { + $$ = make_return_query_stmt($2); + } else { plpgsql_push_back_token(tok); *************** make_return_stmt(int lineno) *** 2104,2110 **** if (plpgsql_curr_compile->fn_retset) { if (yylex() != ';') ! yyerror("RETURN cannot have a parameter in function returning set; use RETURN NEXT"); } else if (plpgsql_curr_compile->out_param_varno >= 0) { --- 2110,2117 ---- if (plpgsql_curr_compile->fn_retset) { if (yylex() != ';') ! yyerror("RETURN cannot have a parameter in function " ! "returning set; use RETURN NEXT or RETURN QUERY"); } else if (plpgsql_curr_compile->out_param_varno >= 0) { *************** make_return_next_stmt(int lineno) *** 2200,2205 **** --- 2207,2229 ---- } + static PLpgSQL_stmt * + make_return_query_stmt(int lineno) + { + PLpgSQL_stmt_return_query *new; + + if (!plpgsql_curr_compile->fn_retset) + yyerror("cannot use RETURN QUERY in a non-SETOF function"); + + new = palloc0(sizeof(PLpgSQL_stmt_return_query)); + new->cmd_type = PLPGSQL_STMT_RETURN_QUERY; + new->lineno = lineno; + new->query = read_sql_construct(';', 0, ")", "", false, true, NULL); + + return (PLpgSQL_stmt *) new; + } + + static void check_assignable(PLpgSQL_datum *datum) { Index: src/pl/plpgsql/src/pl_exec.c =================================================================== RCS file: /home/neilc/postgres/cvs_root/pgsql/src/pl/plpgsql/src/pl_exec.c,v retrieving revision 1.198 diff -p -c -r1.198 pl_exec.c *** src/pl/plpgsql/src/pl_exec.c 15 Jul 2007 02:15:04 -0000 1.198 --- src/pl/plpgsql/src/pl_exec.c 18 Jul 2007 07:51:17 -0000 *************** static int exec_stmt_return(PLpgSQL_exec *** 105,110 **** --- 105,112 ---- PLpgSQL_stmt_return *stmt); static int exec_stmt_return_next(PLpgSQL_execstate *estate, PLpgSQL_stmt_return_next *stmt); + static int exec_stmt_return_query(PLpgSQL_execstate *estate, + PLpgSQL_stmt_return_query *stmt); static int exec_stmt_raise(PLpgSQL_execstate *estate, PLpgSQL_stmt_raise *stmt); static int exec_stmt_execsql(PLpgSQL_execstate *estate, *************** exec_stmt(PLpgSQL_execstate *estate, PLp *** 1244,1249 **** --- 1246,1255 ---- rc = exec_stmt_return_next(estate, (PLpgSQL_stmt_return_next *) stmt); break; + case PLPGSQL_STMT_RETURN_QUERY: + rc = exec_stmt_return_query(estate, (PLpgSQL_stmt_return_query *) stmt); + break; + case PLPGSQL_STMT_RAISE: rc = exec_stmt_raise(estate, (PLpgSQL_stmt_raise *) stmt); break; *************** exec_stmt_return_next(PLpgSQL_execstate *** 2137,2142 **** --- 2143,2201 ---- return PLPGSQL_RC_OK; } + /* ---------- + * exec_stmt_return_query Evaluate a query and add it to the + * list of tuples returned by the current + * SRF. + * ---------- + */ + static int + exec_stmt_return_query(PLpgSQL_execstate *estate, + PLpgSQL_stmt_return_query *stmt) + { + Portal portal; + + if (!estate->retisset) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("cannot use RETURN QUERY in a non-SETOF function"))); + + if (estate->tuple_store == NULL) + exec_init_tuple_store(estate); + + exec_run_select(estate, stmt->query, 0, &portal); + + if (!compatible_tupdesc(estate->rettupdesc, portal->tupDesc)) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("structure of query does not match function result type"))); + + while (true) + { + MemoryContext old_cxt; + int i; + + SPI_cursor_fetch(portal, true, 50); + if (SPI_processed == 0) + break; + + old_cxt = MemoryContextSwitchTo(estate->tuple_store_cxt); + for (i = 0; i < SPI_processed; i++) + { + HeapTuple tuple = SPI_tuptable->vals[i]; + tuplestore_puttuple(estate->tuple_store, tuple); + } + MemoryContextSwitchTo(old_cxt); + + SPI_freetuptable(SPI_tuptable); + } + + SPI_freetuptable(SPI_tuptable); + SPI_cursor_close(portal); + + return PLPGSQL_RC_OK; + } + static void exec_init_tuple_store(PLpgSQL_execstate *estate) { Index: src/pl/plpgsql/src/pl_funcs.c =================================================================== RCS file: /home/neilc/postgres/cvs_root/pgsql/src/pl/plpgsql/src/pl_funcs.c,v retrieving revision 1.62 diff -p -c -r1.62 pl_funcs.c *** src/pl/plpgsql/src/pl_funcs.c 20 Jul 2007 16:23:34 -0000 1.62 --- src/pl/plpgsql/src/pl_funcs.c 24 Jul 2007 06:42:39 -0000 *************** plpgsql_stmt_typename(PLpgSQL_stmt *stmt *** 443,448 **** --- 443,450 ---- return "RETURN"; case PLPGSQL_STMT_RETURN_NEXT: return "RETURN NEXT"; + case PLPGSQL_STMT_RETURN_QUERY: + return "RETURN QUERY"; case PLPGSQL_STMT_RAISE: return "RAISE"; case PLPGSQL_STMT_EXECSQL: *************** static void dump_fors(PLpgSQL_stmt_fors *** 484,489 **** --- 486,492 ---- static void dump_exit(PLpgSQL_stmt_exit *stmt); static void dump_return(PLpgSQL_stmt_return *stmt); static void dump_return_next(PLpgSQL_stmt_return_next *stmt); + static void dump_return_query(PLpgSQL_stmt_return_query *stmt); static void dump_raise(PLpgSQL_stmt_raise *stmt); static void dump_execsql(PLpgSQL_stmt_execsql *stmt); static void dump_dynexecute(PLpgSQL_stmt_dynexecute *stmt); *************** dump_stmt(PLpgSQL_stmt *stmt) *** 542,547 **** --- 545,553 ---- case PLPGSQL_STMT_RETURN_NEXT: dump_return_next((PLpgSQL_stmt_return_next *) stmt); break; + case PLPGSQL_STMT_RETURN_QUERY: + dump_return_query((PLpgSQL_stmt_return_query *) stmt); + break; case PLPGSQL_STMT_RAISE: dump_raise((PLpgSQL_stmt_raise *) stmt); break; *************** dump_return_next(PLpgSQL_stmt_return_nex *** 879,884 **** --- 885,899 ---- } static void + dump_return_query(PLpgSQL_stmt_return_query *stmt) + { + dump_ind(); + printf("RETURN QUERY "); + dump_expr(stmt->query); + printf("\n"); + } + + static void dump_raise(PLpgSQL_stmt_raise *stmt) { ListCell *lc; Index: src/pl/plpgsql/src/plpgsql.h =================================================================== RCS file: /home/neilc/postgres/cvs_root/pgsql/src/pl/plpgsql/src/plpgsql.h,v retrieving revision 1.90 diff -p -c -r1.90 plpgsql.h *** src/pl/plpgsql/src/plpgsql.h 16 Jul 2007 17:01:11 -0000 1.90 --- src/pl/plpgsql/src/plpgsql.h 18 Jul 2007 07:43:38 -0000 *************** enum *** 83,88 **** --- 83,89 ---- PLPGSQL_STMT_EXIT, PLPGSQL_STMT_RETURN, PLPGSQL_STMT_RETURN_NEXT, + PLPGSQL_STMT_RETURN_QUERY, PLPGSQL_STMT_RAISE, PLPGSQL_STMT_EXECSQL, PLPGSQL_STMT_DYNEXECUTE, *************** typedef struct *** 494,499 **** --- 495,507 ---- } PLpgSQL_stmt_return_next; typedef struct + { /* RETURN QUERY statement */ + int cmd_type; + int lineno; + PLpgSQL_expr *query; + } PLpgSQL_stmt_return_query; + + typedef struct { /* RAISE statement */ int cmd_type; int lineno; Index: src/pl/plpgsql/src/scan.l =================================================================== RCS file: /home/neilc/postgres/cvs_root/pgsql/src/pl/plpgsql/src/scan.l,v retrieving revision 1.57 diff -p -c -r1.57 scan.l *** src/pl/plpgsql/src/scan.l 29 Apr 2007 01:21:09 -0000 1.57 --- src/pl/plpgsql/src/scan.l 18 Jul 2007 07:43:38 -0000 *************** declare { return K_DECLARE; } *** 124,131 **** default { return K_DEFAULT; } diagnostics { return K_DIAGNOSTICS; } else { return K_ELSE; } ! elseif { return K_ELSIF; } ! elsif { return K_ELSIF; } end { return K_END; } exception { return K_EXCEPTION; } execute { return K_EXECUTE; } --- 124,131 ---- default { return K_DEFAULT; } diagnostics { return K_DIAGNOSTICS; } else { return K_ELSE; } ! elseif { return K_ELSIF; } ! elsif { return K_ELSIF; } end { return K_END; } exception { return K_EXCEPTION; } execute { return K_EXECUTE; } *************** null { return K_NULL; } *** 151,156 **** --- 151,157 ---- open { return K_OPEN; } or { return K_OR; } perform { return K_PERFORM; } + query { return K_QUERY; } raise { return K_RAISE; } rename { return K_RENAME; } result_oid { return K_RESULT_OID; } Index: src/test/regress/expected/plpgsql.out =================================================================== RCS file: /home/neilc/postgres/cvs_root/pgsql/src/test/regress/expected/plpgsql.out,v retrieving revision 1.57 diff -p -c -r1.57 plpgsql.out *** src/test/regress/expected/plpgsql.out 20 Jul 2007 16:38:38 -0000 1.57 --- src/test/regress/expected/plpgsql.out 24 Jul 2007 06:35:59 -0000 *************** NOTICE: innerblock.param1 = 2 *** 3079,3081 **** --- 3079,3130 ---- (1 row) drop function pl_qual_names(int); + -- tests for RETURN QUERY + create function ret_query1(out int, out int) returns setof record as $$ + begin + $1 := -1; + $2 := -2; + return next; + return query select x + 1, x * 10 from generate_series(0, 10) s (x); + return next; + end; + $$ language plpgsql; + select * from ret_query1(); + column1 | column2 + ---------+--------- + -1 | -2 + 1 | 0 + 2 | 10 + 3 | 20 + 4 | 30 + 5 | 40 + 6 | 50 + 7 | 60 + 8 | 70 + 9 | 80 + 10 | 90 + 11 | 100 + -1 | -2 + (13 rows) + + create type record_type as (x text, y int, z boolean); + create or replace function ret_query2(lim int) returns setof record_type as $$ + begin + return query select md5(s.x::text), s.x, s.x > 0 + from generate_series(-8, lim) s (x) where s.x % 2 = 0; + end; + $$ language plpgsql; + select * from ret_query2(8); + x | y | z + ----------------------------------+----+--- + a8d2ec85eaf98407310b72eb73dda247 | -8 | f + 596a3d04481816330f07e4f97510c28f | -6 | f + 0267aaf632e87a63288a08331f22c7c3 | -4 | f + 5d7b9adcbe1c629ec722529dd12e5129 | -2 | f + cfcd208495d565ef66e7dff9f98764da | 0 | f + c81e728d9d4c2f636f067f89cc14862c | 2 | t + a87ff679a2f3e71d9181a67b7542122c | 4 | t + 1679091c5a880faf6fb5e6087eb1b2dc | 6 | t + c9f0f895fb98ab9159f51fd0297e236d | 8 | t + (9 rows) + Index: src/test/regress/sql/plpgsql.sql =================================================================== RCS file: /home/neilc/postgres/cvs_root/pgsql/src/test/regress/sql/plpgsql.sql,v retrieving revision 1.48 diff -p -c -r1.48 plpgsql.sql *** src/test/regress/sql/plpgsql.sql 16 Jul 2007 17:01:11 -0000 1.48 --- src/test/regress/sql/plpgsql.sql 24 Jul 2007 06:33:55 -0000 *************** $$ language plpgsql; *** 2557,2559 **** --- 2557,2583 ---- select pl_qual_names(42); drop function pl_qual_names(int); + + -- tests for RETURN QUERY + create function ret_query1(out int, out int) returns setof record as $$ + begin + $1 := -1; + $2 := -2; + return next; + return query select x + 1, x * 10 from generate_series(0, 10) s (x); + return next; + end; + $$ language plpgsql; + + select * from ret_query1(); + + create type record_type as (x text, y int, z boolean); + + create or replace function ret_query2(lim int) returns setof record_type as $$ + begin + return query select md5(s.x::text), s.x, s.x > 0 + from generate_series(-8, lim) s (x) where s.x % 2 = 0; + end; + $$ language plpgsql; + + select * from ret_query2(8); \ No newline at end of file
---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org