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.
Advertising
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
