Skip to content

Commit b2b9b4d

Browse files
author
Neil Conway
committed
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.
1 parent 507b53c commit b2b9b4d

File tree

8 files changed

+236
-37
lines changed

8 files changed

+236
-37
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 48 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.115 2007/07/16 17:01:10 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.116 2007/07/25 04:19:08 neilc Exp $ -->
22

33
<chapter id="plpgsql">
44
<title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title>
@@ -135,7 +135,9 @@
135135
<application>PL/pgSQL</> functions can also be declared to return
136136
a <quote>set</>, or table, of any data type they can return a single
137137
instance of. Such a function generates its output by executing
138-
<literal>RETURN NEXT</> for each desired element of the result set.
138+
<command>RETURN NEXT</> for each desired element of the result
139+
set, or by using <command>RETURN QUERY</> to output the result of
140+
evaluating a query.
139141
</para>
140142

141143
<para>
@@ -1349,52 +1351,69 @@ RETURN <replaceable>expression</replaceable>;
13491351
</sect3>
13501352

13511353
<sect3>
1352-
<title><command>RETURN NEXT</></title>
1354+
<title><command>RETURN NEXT</> and <command>RETURN QUERY</command></title>
1355+
<indexterm>
1356+
<primary>RETURN NEXT</primary>
1357+
<secondary>in PL/PgSQL</secondary>
1358+
</indexterm>
1359+
<indexterm>
1360+
<primary>RETURN QUERY</primary>
1361+
<secondary>in PL/PgSQL</secondary>
1362+
</indexterm>
13531363

13541364
<synopsis>
13551365
RETURN NEXT <replaceable>expression</replaceable>;
1366+
RETURN QUERY <replaceable>query</replaceable>;
13561367
</synopsis>
13571368

13581369
<para>
13591370
When a <application>PL/pgSQL</> function is declared to return
13601371
<literal>SETOF <replaceable>sometype</></literal>, the procedure
13611372
to follow is slightly different. In that case, the individual
1362-
items to return are specified in <command>RETURN NEXT</command>
1363-
commands, and then a final <command>RETURN</command> command
1364-
with no argument is used to indicate that the function has
1365-
finished executing. <command>RETURN NEXT</command> can be used
1366-
with both scalar and composite data types; with a composite result
1367-
type, an entire <quote>table</quote> of results will be returned.
1373+
items to return are specified by a sequence of <command>RETURN
1374+
NEXT</command> or <command>RETURN QUERY</command> commands, and
1375+
then a final <command>RETURN</command> command with no argument
1376+
is used to indicate that the function has finished executing.
1377+
<command>RETURN NEXT</command> can be used with both scalar and
1378+
composite data types; with a composite result type, an entire
1379+
<quote>table</quote> of results will be returned.
1380+
<command>RETURN QUERY</command> appends the results of executing
1381+
a query to the function's result set. <command>RETURN
1382+
NEXT</command> and <command>RETURN QUERY</command> can be freely
1383+
intermixed in a single set-returning function, in which case
1384+
their results will be concatenated.
13681385
</para>
13691386

13701387
<para>
1371-
<command>RETURN NEXT</command> does not actually return from the
1372-
function &mdash; it simply saves away the value of the expression.
1373-
Execution then continues with the next statement in
1374-
the <application>PL/pgSQL</> function. As successive
1375-
<command>RETURN NEXT</command> commands are executed, the result
1376-
set is built up. A final <command>RETURN</command>, which should
1377-
have no argument, causes control to exit the function (or you can
1378-
just let control reach the end of the function).
1388+
<command>RETURN NEXT</command> and <command>RETURN
1389+
QUERY</command> do not actually return from the function &mdash;
1390+
they simply append zero or more rows to the function's result
1391+
set. Execution then continues with the next statement in the
1392+
<application>PL/pgSQL</> function. As successive
1393+
<command>RETURN NEXT</command> or <command>RETURN
1394+
QUERY</command> commands are executed, the result set is built
1395+
up. A final <command>RETURN</command>, which should have no
1396+
argument, causes control to exit the function (or you can just
1397+
let control reach the end of the function).
13791398
</para>
13801399

13811400
<para>
13821401
If you declared the function with output parameters, write just
13831402
<command>RETURN NEXT</command> with no expression. On each
1384-
execution, the current values
1385-
of the output parameter variable(s) will be saved for eventual return
1386-
as a row of the result.
1387-
Note that you must declare the function as returning
1388-
<literal>SETOF record</literal> when there are
1389-
multiple output parameters, or
1390-
<literal>SETOF <replaceable>sometype</></literal> when there is
1391-
just one output parameter of type <replaceable>sometype</>, in
1392-
order to create a set-returning function with output parameters.
1403+
execution, the current values of the output parameter
1404+
variable(s) will be saved for eventual return as a row of the
1405+
result. Note that you must declare the function as returning
1406+
<literal>SETOF record</literal> when there are multiple output
1407+
parameters, or <literal>SETOF <replaceable>sometype</></literal>
1408+
when there is just one output parameter of type
1409+
<replaceable>sometype</>, in order to create a set-returning
1410+
function with output parameters.
13931411
</para>
13941412

13951413
<para>
1396-
Functions that use <command>RETURN NEXT</command> should be
1397-
called in the following fashion:
1414+
Functions that use <command>RETURN NEXT</command> or
1415+
<command>RETURN QUERY</command> should be called in the
1416+
following fashion:
13981417

13991418
<programlisting>
14001419
SELECT * FROM some_func();
@@ -1407,7 +1426,7 @@ SELECT * FROM some_func();
14071426
<note>
14081427
<para>
14091428
The current implementation of <command>RETURN NEXT</command>
1410-
for <application>PL/pgSQL</> stores the entire result set
1429+
and <command>RETURN QUERY</command> stores the entire result set
14111430
before returning from the function, as discussed above. That
14121431
means that if a <application>PL/pgSQL</> function produces a
14131432
very large result set, performance might be poor: data will be

‎src/pl/plpgsql/src/gram.y

Lines changed: 26 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.104 2007/07/16 17:01:10 tgl Exp $
12+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.105 2007/07/25 04:19:08 neilc Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -32,6 +32,7 @@ static PLpgSQL_stmt *make_execsql_stmt(const char *sqlstart, int lineno);
3232
static PLpgSQL_stmt_fetch *read_fetch_direction(void);
3333
static PLpgSQL_stmt *make_return_stmt(int lineno);
3434
static PLpgSQL_stmt *make_return_next_stmt(int lineno);
35+
static PLpgSQL_stmt *make_return_query_stmt(int lineno);
3536
static void check_assignable(PLpgSQL_datum *datum);
3637
static void read_into_target(PLpgSQL_rec **rec, PLpgSQL_row **row,
3738
bool *strict);
@@ -187,6 +188,7 @@ static void check_labels(const char *start_label,
187188
%token K_NULL
188189
%token K_OPEN
189190
%token K_OR
191+
%token K_QUERY
190192
%token K_PERFORM
191193
%token K_ROW_COUNT
192194
%token K_RAISE
@@ -1171,6 +1173,10 @@ stmt_return : K_RETURN lno
11711173
{
11721174
$$ = make_return_next_stmt($2);
11731175
}
1176+
else if (tok == K_QUERY)
1177+
{
1178+
$$ = make_return_query_stmt($2);
1179+
}
11741180
else
11751181
{
11761182
plpgsql_push_back_token(tok);
@@ -2104,7 +2110,8 @@ make_return_stmt(int lineno)
21042110
if (plpgsql_curr_compile->fn_retset)
21052111
{
21062112
if (yylex() != ';')
2107-
yyerror("RETURN cannot have a parameter in function returning set; use RETURN NEXT");
2113+
yyerror("RETURN cannot have a parameter in function "
2114+
"returning set; use RETURN NEXT or RETURN QUERY");
21082115
}
21092116
else if (plpgsql_curr_compile->out_param_varno >= 0)
21102117
{
@@ -2200,6 +2207,23 @@ make_return_next_stmt(int lineno)
22002207
}
22012208

22022209

2210+
static PLpgSQL_stmt *
2211+
make_return_query_stmt(int lineno)
2212+
{
2213+
PLpgSQL_stmt_return_query *new;
2214+
2215+
if (!plpgsql_curr_compile->fn_retset)
2216+
yyerror("cannot use RETURN QUERY in a non-SETOF function");
2217+
2218+
new = palloc0(sizeof(PLpgSQL_stmt_return_query));
2219+
new->cmd_type = PLPGSQL_STMT_RETURN_QUERY;
2220+
new->lineno = lineno;
2221+
new->query = read_sql_construct(';', 0, ")", "", false, true, NULL);
2222+
2223+
return (PLpgSQL_stmt *) new;
2224+
}
2225+
2226+
22032227
static void
22042228
check_assignable(PLpgSQL_datum *datum)
22052229
{

‎src/pl/plpgsql/src/pl_exec.c

Lines changed: 60 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.198 2007/07/15 02:15:04 tgl Exp $
11+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.199 2007/07/25 04:19:08 neilc Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -105,6 +105,8 @@ static int exec_stmt_return(PLpgSQL_execstate *estate,
105105
PLpgSQL_stmt_return *stmt);
106106
static int exec_stmt_return_next(PLpgSQL_execstate *estate,
107107
PLpgSQL_stmt_return_next *stmt);
108+
static int exec_stmt_return_query(PLpgSQL_execstate *estate,
109+
PLpgSQL_stmt_return_query *stmt);
108110
static int exec_stmt_raise(PLpgSQL_execstate *estate,
109111
PLpgSQL_stmt_raise *stmt);
110112
static int exec_stmt_execsql(PLpgSQL_execstate *estate,
@@ -1244,6 +1246,10 @@ exec_stmt(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt)
12441246
rc = exec_stmt_return_next(estate, (PLpgSQL_stmt_return_next *) stmt);
12451247
break;
12461248

1249+
case PLPGSQL_STMT_RETURN_QUERY:
1250+
rc = exec_stmt_return_query(estate, (PLpgSQL_stmt_return_query *) stmt);
1251+
break;
1252+
12471253
case PLPGSQL_STMT_RAISE:
12481254
rc = exec_stmt_raise(estate, (PLpgSQL_stmt_raise *) stmt);
12491255
break;
@@ -2137,6 +2143,59 @@ exec_stmt_return_next(PLpgSQL_execstate *estate,
21372143
return PLPGSQL_RC_OK;
21382144
}
21392145

2146+
/* ----------
2147+
* exec_stmt_return_query Evaluate a query and add it to the
2148+
* list of tuples returned by the current
2149+
* SRF.
2150+
* ----------
2151+
*/
2152+
static int
2153+
exec_stmt_return_query(PLpgSQL_execstate *estate,
2154+
PLpgSQL_stmt_return_query *stmt)
2155+
{
2156+
Portal portal;
2157+
2158+
if (!estate->retisset)
2159+
ereport(ERROR,
2160+
(errcode(ERRCODE_SYNTAX_ERROR),
2161+
errmsg("cannot use RETURN QUERY in a non-SETOF function")));
2162+
2163+
if (estate->tuple_store == NULL)
2164+
exec_init_tuple_store(estate);
2165+
2166+
exec_run_select(estate, stmt->query, 0, &portal);
2167+
2168+
if (!compatible_tupdesc(estate->rettupdesc, portal->tupDesc))
2169+
ereport(ERROR,
2170+
(errcode(ERRCODE_DATATYPE_MISMATCH),
2171+
errmsg("structure of query does not match function result type")));
2172+
2173+
while (true)
2174+
{
2175+
MemoryContext old_cxt;
2176+
int i;
2177+
2178+
SPI_cursor_fetch(portal, true, 50);
2179+
if (SPI_processed == 0)
2180+
break;
2181+
2182+
old_cxt = MemoryContextSwitchTo(estate->tuple_store_cxt);
2183+
for (i = 0; i < SPI_processed; i++)
2184+
{
2185+
HeapTuple tuple = SPI_tuptable->vals[i];
2186+
tuplestore_puttuple(estate->tuple_store, tuple);
2187+
}
2188+
MemoryContextSwitchTo(old_cxt);
2189+
2190+
SPI_freetuptable(SPI_tuptable);
2191+
}
2192+
2193+
SPI_freetuptable(SPI_tuptable);
2194+
SPI_cursor_close(portal);
2195+
2196+
return PLPGSQL_RC_OK;
2197+
}
2198+
21402199
static void
21412200
exec_init_tuple_store(PLpgSQL_execstate *estate)
21422201
{

‎src/pl/plpgsql/src/pl_funcs.c

Lines changed: 16 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_funcs.c,v 1.62 2007/07/20 16:23:34 petere Exp $
11+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_funcs.c,v 1.63 2007/07/25 04:19:08 neilc Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -443,6 +443,8 @@ plpgsql_stmt_typename(PLpgSQL_stmt *stmt)
443443
return "RETURN";
444444
case PLPGSQL_STMT_RETURN_NEXT:
445445
return "RETURN NEXT";
446+
case PLPGSQL_STMT_RETURN_QUERY:
447+
return "RETURN QUERY";
446448
case PLPGSQL_STMT_RAISE:
447449
return "RAISE";
448450
case PLPGSQL_STMT_EXECSQL:
@@ -484,6 +486,7 @@ static void dump_fors(PLpgSQL_stmt_fors *stmt);
484486
static void dump_exit(PLpgSQL_stmt_exit *stmt);
485487
static void dump_return(PLpgSQL_stmt_return *stmt);
486488
static void dump_return_next(PLpgSQL_stmt_return_next *stmt);
489+
static void dump_return_query(PLpgSQL_stmt_return_query *stmt);
487490
static void dump_raise(PLpgSQL_stmt_raise *stmt);
488491
static void dump_execsql(PLpgSQL_stmt_execsql *stmt);
489492
static void dump_dynexecute(PLpgSQL_stmt_dynexecute *stmt);
@@ -542,6 +545,9 @@ dump_stmt(PLpgSQL_stmt *stmt)
542545
case PLPGSQL_STMT_RETURN_NEXT:
543546
dump_return_next((PLpgSQL_stmt_return_next *) stmt);
544547
break;
548+
case PLPGSQL_STMT_RETURN_QUERY:
549+
dump_return_query((PLpgSQL_stmt_return_query *) stmt);
550+
break;
545551
case PLPGSQL_STMT_RAISE:
546552
dump_raise((PLpgSQL_stmt_raise *) stmt);
547553
break;
@@ -878,6 +884,15 @@ dump_return_next(PLpgSQL_stmt_return_next *stmt)
878884
printf("\n");
879885
}
880886

887+
static void
888+
dump_return_query(PLpgSQL_stmt_return_query *stmt)
889+
{
890+
dump_ind();
891+
printf("RETURN QUERY ");
892+
dump_expr(stmt->query);
893+
printf("\n");
894+
}
895+
881896
static void
882897
dump_raise(PLpgSQL_stmt_raise *stmt)
883898
{

‎src/pl/plpgsql/src/plpgsql.h

Lines changed: 9 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.90 2007/07/16 17:01:11 tgl Exp $
11+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.91 2007/07/25 04:19:09 neilc Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -83,6 +83,7 @@ enum
8383
PLPGSQL_STMT_EXIT,
8484
PLPGSQL_STMT_RETURN,
8585
PLPGSQL_STMT_RETURN_NEXT,
86+
PLPGSQL_STMT_RETURN_QUERY,
8687
PLPGSQL_STMT_RAISE,
8788
PLPGSQL_STMT_EXECSQL,
8889
PLPGSQL_STMT_DYNEXECUTE,
@@ -493,6 +494,13 @@ typedef struct
493494
int retvarno;
494495
} PLpgSQL_stmt_return_next;
495496

497+
typedef struct
498+
{ /* RETURN QUERY statement */
499+
int cmd_type;
500+
int lineno;
501+
PLpgSQL_expr *query;
502+
} PLpgSQL_stmt_return_query;
503+
496504
typedef struct
497505
{ /* RAISE statement */
498506
int cmd_type;

‎src/pl/plpgsql/src/scan.l

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/scan.l,v 1.57 2007/04/29 01:21:09 neilc Exp $
12+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/scan.l,v 1.58 2007/07/25 04:19:09 neilc Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -124,8 +124,8 @@ declare { return K_DECLARE; }
124124
default { return K_DEFAULT; }
125125
diagnostics { return K_DIAGNOSTICS; }
126126
else { return K_ELSE; }
127-
elseif { return K_ELSIF; }
128-
elsif { return K_ELSIF; }
127+
elseif { return K_ELSIF; }
128+
elsif { return K_ELSIF; }
129129
end { return K_END; }
130130
exception { return K_EXCEPTION; }
131131
execute { return K_EXECUTE; }
@@ -151,6 +151,7 @@ null { return K_NULL; }
151151
open { return K_OPEN; }
152152
or { return K_OR; }
153153
perform { return K_PERFORM; }
154+
query { return K_QUERY; }
154155
raise { return K_RAISE; }
155156
rename { return K_RENAME; }
156157
result_oid { return K_RESULT_OID; }

0 commit comments

Comments
 (0)