Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitb2b9b4d

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 resultof evaluating a query; it should also be more efficient than repeatedRETURN NEXT statements. Based on an earlier patch from Pavel Stehule.
1 parent507b53c commitb2b9b4d

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 @@ staticPLpgSQL_stmt*make_execsql_stmt(const char *sqlstart, int lineno);
3232
staticPLpgSQL_stmt_fetch *read_fetch_direction(void);
3333
staticPLpgSQL_stmt*make_return_stmt(int lineno);
3434
staticPLpgSQL_stmt*make_return_next_stmt(int lineno);
35+
staticPLpgSQL_stmt*make_return_query_stmt(int lineno);
3536
staticvoidcheck_assignable(PLpgSQL_datum *datum);
3637
staticvoidread_into_target(PLpgSQL_rec **rec, PLpgSQL_row **row,
3738
bool *strict);
@@ -187,6 +188,7 @@ staticvoid check_labels(const char *start_label,
187188
%tokenK_NULL
188189
%tokenK_OPEN
189190
%tokenK_OR
191+
%tokenK_QUERY
190192
%tokenK_PERFORM
191193
%tokenK_ROW_COUNT
192194
%tokenK_RAISE
@@ -1171,6 +1173,10 @@ stmt_return: K_RETURN lno
11711173
{
11721174
$$ = make_return_next_stmt($2);
11731175
}
1176+
elseif (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
elseif (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
staticvoid
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
staticintexec_stmt_return_next(PLpgSQL_execstate*estate,
107107
PLpgSQL_stmt_return_next*stmt);
108+
staticintexec_stmt_return_query(PLpgSQL_execstate*estate,
109+
PLpgSQL_stmt_return_query*stmt);
108110
staticintexec_stmt_raise(PLpgSQL_execstate*estate,
109111
PLpgSQL_stmt_raise*stmt);
110112
staticintexec_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+
casePLPGSQL_STMT_RETURN_QUERY:
1250+
rc=exec_stmt_return_query(estate, (PLpgSQL_stmt_return_query*)stmt);
1251+
break;
1252+
12471253
casePLPGSQL_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
returnPLPGSQL_RC_OK;
21382144
}
21392145

2146+
/* ----------
2147+
* exec_stmt_return_queryEvaluate a query and add it to the
2148+
*list of tuples returned by the current
2149+
*SRF.
2150+
* ----------
2151+
*/
2152+
staticint
2153+
exec_stmt_return_query(PLpgSQL_execstate*estate,
2154+
PLpgSQL_stmt_return_query*stmt)
2155+
{
2156+
Portalportal;
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+
MemoryContextold_cxt;
2176+
inti;
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+
HeapTupletuple=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+
returnPLPGSQL_RC_OK;
2197+
}
2198+
21402199
staticvoid
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
casePLPGSQL_STMT_RETURN_NEXT:
445445
return"RETURN NEXT";
446+
casePLPGSQL_STMT_RETURN_QUERY:
447+
return"RETURN QUERY";
446448
casePLPGSQL_STMT_RAISE:
447449
return"RAISE";
448450
casePLPGSQL_STMT_EXECSQL:
@@ -484,6 +486,7 @@ static void dump_fors(PLpgSQL_stmt_fors *stmt);
484486
staticvoiddump_exit(PLpgSQL_stmt_exit*stmt);
485487
staticvoiddump_return(PLpgSQL_stmt_return*stmt);
486488
staticvoiddump_return_next(PLpgSQL_stmt_return_next*stmt);
489+
staticvoiddump_return_query(PLpgSQL_stmt_return_query*stmt);
487490
staticvoiddump_raise(PLpgSQL_stmt_raise*stmt);
488491
staticvoiddump_execsql(PLpgSQL_stmt_execsql*stmt);
489492
staticvoiddump_dynexecute(PLpgSQL_stmt_dynexecute*stmt);
@@ -542,6 +545,9 @@ dump_stmt(PLpgSQL_stmt *stmt)
542545
casePLPGSQL_STMT_RETURN_NEXT:
543546
dump_return_next((PLpgSQL_stmt_return_next*)stmt);
544547
break;
548+
casePLPGSQL_STMT_RETURN_QUERY:
549+
dump_return_query((PLpgSQL_stmt_return_query*)stmt);
550+
break;
545551
casePLPGSQL_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+
staticvoid
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
staticvoid
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
intretvarno;
494495
}PLpgSQL_stmt_return_next;
495496

497+
typedefstruct
498+
{/* RETURN QUERY statement */
499+
intcmd_type;
500+
intlineno;
501+
PLpgSQL_expr*query;
502+
}PLpgSQL_stmt_return_query;
503+
496504
typedefstruct
497505
{/* RAISE statement*/
498506
intcmd_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)
[8]ページ先頭
©2009-2025 Movatter.jp