Skip to content

Commit 4739159

Browse files
tglsfdctglsfdc
committed
Support RETURN QUERY EXECUTE in plpgsql.
Pavel Stehule
1 parent 511b798 commit 4739159

File tree

7 files changed

+185
-62
lines changed

7 files changed

+185
-62
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 10 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.127 2008/04/06 23:43:29 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.128 2008/05/03 00:11:36 tgl Exp $ -->
22

33
<chapter id="plpgsql">
44
<title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title>
@@ -1467,6 +1467,7 @@ RETURN <replaceable>expression</replaceable>;
14671467
<synopsis>
14681468
RETURN NEXT <replaceable>expression</replaceable>;
14691469
RETURN QUERY <replaceable>query</replaceable>;
1470+
RETURN QUERY EXECUTE <replaceable class="command">command-string</replaceable> <optional> USING <replaceable>expression</replaceable> <optional>, ...</optional> </optional>;
14701471
</synopsis>
14711472

14721473
<para>
@@ -1500,6 +1501,14 @@ RETURN QUERY <replaceable>query</replaceable>;
15001501
let control reach the end of the function).
15011502
</para>
15021503

1504+
<para>
1505+
<command>RETURN QUERY</command> has a variant
1506+
<command>RETURN QUERY EXECUTE</command>, which specifies the
1507+
query to be executed dynamically. Parameter expressions can
1508+
be inserted into the computed query string via <literal>USING</>,
1509+
in just the same way as in the <command>EXECUTE</> command.
1510+
</para>
1511+
15031512
<para>
15041513
If you declared the function with output parameters, write just
15051514
<command>RETURN NEXT</command> with no expression. On each
@@ -1544,7 +1553,6 @@ SELECT * FROM getallfoo();
15441553
Note that functions using <command>RETURN NEXT</command> or
15451554
<command>RETURN QUERY</command> must be called as a table source in
15461555
a <literal>FROM</literal> clause.
1547-
15481556
</para>
15491557

15501558
<note>

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

Lines changed: 28 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.110 2008/04/06 23:43:29 tgl Exp $
12+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.111 2008/05/03 00:11:36 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -2239,14 +2239,40 @@ static PLpgSQL_stmt *
22392239
make_return_query_stmt(int lineno)
22402240
{
22412241
PLpgSQL_stmt_return_query *new;
2242+
int tok;
22422243

22432244
if (!plpgsql_curr_compile->fn_retset)
22442245
yyerror("cannot use RETURN QUERY in a non-SETOF function");
22452246

22462247
new = palloc0(sizeof(PLpgSQL_stmt_return_query));
22472248
new->cmd_type = PLPGSQL_STMT_RETURN_QUERY;
22482249
new->lineno = lineno;
2249-
new->query = read_sql_stmt("");
2250+
2251+
/* check for RETURN QUERY EXECUTE */
2252+
if ((tok = yylex()) != K_EXECUTE)
2253+
{
2254+
/* ordinary static query */
2255+
plpgsql_push_back_token(tok);
2256+
new->query = read_sql_stmt("");
2257+
}
2258+
else
2259+
{
2260+
/* dynamic SQL */
2261+
int term;
2262+
2263+
new->dynquery = read_sql_expression2(';', K_USING, "; or USING",
2264+
&term);
2265+
if (term == K_USING)
2266+
{
2267+
do
2268+
{
2269+
PLpgSQL_expr *expr;
2270+
2271+
expr = read_sql_expression2(',', ';', ", or ;", &term);
2272+
new->params = lappend(new->params, expr);
2273+
} while (term == ',');
2274+
}
2275+
}
22502276

22512277
return (PLpgSQL_stmt *) new;
22522278
}

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

Lines changed: 79 additions & 52 deletions
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.210 2008/04/17 21:37:28 alvherre Exp $
11+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.211 2008/05/03 00:11:36 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -197,6 +197,8 @@ static void assign_text_var(PLpgSQL_var *var, const char *str);
197197
static PreparedParamsData *exec_eval_using_params(PLpgSQL_execstate *estate,
198198
List *params);
199199
static void free_params_data(PreparedParamsData *ppd);
200+
static Portal exec_dynquery_with_params(PLpgSQL_execstate *estate,
201+
PLpgSQL_expr *query, List *params);
200202

201203

202204
/* ----------
@@ -1968,7 +1970,7 @@ exec_stmt_return(PLpgSQL_execstate *estate, PLpgSQL_stmt_return *stmt)
19681970
PLpgSQL_row *row = (PLpgSQL_row *) retvar;
19691971

19701972
Assert(row->rowtupdesc);
1971-
estate->retval =
1973+
estate->retval =
19721974
PointerGetDatum(make_tuple_from_row(estate, row,
19731975
row->rowtupdesc));
19741976
if (DatumGetPointer(estate->retval) == NULL) /* should not happen */
@@ -2189,7 +2191,18 @@ exec_stmt_return_query(PLpgSQL_execstate *estate,
21892191
if (estate->tuple_store == NULL)
21902192
exec_init_tuple_store(estate);
21912193

2192-
exec_run_select(estate, stmt->query, 0, &portal);
2194+
if (stmt->query != NULL)
2195+
{
2196+
/* static query */
2197+
exec_run_select(estate, stmt->query, 0, &portal);
2198+
}
2199+
else
2200+
{
2201+
/* RETURN QUERY EXECUTE */
2202+
Assert(stmt->dynquery != NULL);
2203+
portal = exec_dynquery_with_params(estate, stmt->dynquery,
2204+
stmt->params);
2205+
}
21932206

21942207
if (!compatible_tupdesc(estate->rettupdesc, portal->tupDesc))
21952208
ereport(ERROR,
@@ -2841,58 +2854,10 @@ exec_stmt_dynexecute(PLpgSQL_execstate *estate,
28412854
static int
28422855
exec_stmt_dynfors(PLpgSQL_execstate *estate, PLpgSQL_stmt_dynfors *stmt)
28432856
{
2844-
Datum query;
2845-
bool isnull;
2846-
Oid restype;
2847-
char *querystr;
28482857
Portal portal;
28492858
int rc;
28502859

2851-
/*
2852-
* Evaluate the string expression after the EXECUTE keyword. It's result
2853-
* is the querystring we have to execute.
2854-
*/
2855-
query = exec_eval_expr(estate, stmt->query, &isnull, &restype);
2856-
if (isnull)
2857-
ereport(ERROR,
2858-
(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
2859-
errmsg("cannot EXECUTE a null querystring")));
2860-
2861-
/* Get the C-String representation */
2862-
querystr = convert_value_to_string(query, restype);
2863-
2864-
exec_eval_cleanup(estate);
2865-
2866-
/*
2867-
* Open an implicit cursor for the query. We use SPI_cursor_open_with_args
2868-
* even when there are no params, because this avoids making and freeing
2869-
* one copy of the plan.
2870-
*/
2871-
if (stmt->params)
2872-
{
2873-
PreparedParamsData *ppd;
2874-
2875-
ppd = exec_eval_using_params(estate, stmt->params);
2876-
portal = SPI_cursor_open_with_args(NULL,
2877-
querystr,
2878-
ppd->nargs, ppd->types,
2879-
ppd->values, ppd->nulls,
2880-
estate->readonly_func, 0);
2881-
free_params_data(ppd);
2882-
}
2883-
else
2884-
{
2885-
portal = SPI_cursor_open_with_args(NULL,
2886-
querystr,
2887-
0, NULL,
2888-
NULL, NULL,
2889-
estate->readonly_func, 0);
2890-
}
2891-
2892-
if (portal == NULL)
2893-
elog(ERROR, "could not open implicit cursor for query \"%s\": %s",
2894-
querystr, SPI_result_code_string(SPI_result));
2895-
pfree(querystr);
2860+
portal = exec_dynquery_with_params(estate, stmt->query, stmt->params);
28962861

28972862
/*
28982863
* Execute the loop
@@ -5208,3 +5173,65 @@ free_params_data(PreparedParamsData *ppd)
52085173

52095174
pfree(ppd);
52105175
}
5176+
5177+
/*
5178+
* Open portal for dynamic query
5179+
*/
5180+
static Portal
5181+
exec_dynquery_with_params(PLpgSQL_execstate *estate, PLpgSQL_expr *dynquery,
5182+
List *params)
5183+
{
5184+
Portal portal;
5185+
Datum query;
5186+
bool isnull;
5187+
Oid restype;
5188+
char *querystr;
5189+
5190+
/*
5191+
* Evaluate the string expression after the EXECUTE keyword. Its result
5192+
* is the querystring we have to execute.
5193+
*/
5194+
query = exec_eval_expr(estate, dynquery, &isnull, &restype);
5195+
if (isnull)
5196+
ereport(ERROR,
5197+
(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
5198+
errmsg("cannot EXECUTE a null querystring")));
5199+
5200+
/* Get the C-String representation */
5201+
querystr = convert_value_to_string(query, restype);
5202+
5203+
exec_eval_cleanup(estate);
5204+
5205+
/*
5206+
* Open an implicit cursor for the query. We use SPI_cursor_open_with_args
5207+
* even when there are no params, because this avoids making and freeing
5208+
* one copy of the plan.
5209+
*/
5210+
if (params)
5211+
{
5212+
PreparedParamsData *ppd;
5213+
5214+
ppd = exec_eval_using_params(estate, params);
5215+
portal = SPI_cursor_open_with_args(NULL,
5216+
querystr,
5217+
ppd->nargs, ppd->types,
5218+
ppd->values, ppd->nulls,
5219+
estate->readonly_func, 0);
5220+
free_params_data(ppd);
5221+
}
5222+
else
5223+
{
5224+
portal = SPI_cursor_open_with_args(NULL,
5225+
querystr,
5226+
0, NULL,
5227+
NULL, NULL,
5228+
estate->readonly_func, 0);
5229+
}
5230+
5231+
if (portal == NULL)
5232+
elog(ERROR, "could not open implicit cursor for query \"%s\": %s",
5233+
querystr, SPI_result_code_string(SPI_result));
5234+
pfree(querystr);
5235+
5236+
return portal;
5237+
}

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

Lines changed: 32 additions & 4 deletions
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.69 2008/04/06 23:43:29 tgl Exp $
11+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_funcs.c,v 1.70 2008/05/03 00:11:36 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -963,9 +963,37 @@ static void
963963
dump_return_query(PLpgSQL_stmt_return_query *stmt)
964964
{
965965
dump_ind();
966-
printf("RETURN QUERY ");
967-
dump_expr(stmt->query);
968-
printf("\n");
966+
if (stmt->query)
967+
{
968+
printf("RETURN QUERY ");
969+
dump_expr(stmt->query);
970+
printf("\n");
971+
}
972+
else
973+
{
974+
printf("RETURN QUERY EXECUTE ");
975+
dump_expr(stmt->dynquery);
976+
printf("\n");
977+
if (stmt->params != NIL)
978+
{
979+
ListCell *lc;
980+
int i;
981+
982+
dump_indent += 2;
983+
dump_ind();
984+
printf(" USING\n");
985+
dump_indent += 2;
986+
i = 1;
987+
foreach(lc, stmt->params)
988+
{
989+
dump_ind();
990+
printf(" parameter $%d: ", i++);
991+
dump_expr((PLpgSQL_expr *) lfirst(lc));
992+
printf("\n");
993+
}
994+
dump_indent -= 4;
995+
}
996+
}
969997
}
970998

971999
static void

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

Lines changed: 4 additions & 2 deletions
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.97 2008/04/06 23:43:29 tgl Exp $
11+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.98 2008/05/03 00:11:36 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -529,7 +529,9 @@ typedef struct
529529
{ /* RETURN QUERY statement */
530530
int cmd_type;
531531
int lineno;
532-
PLpgSQL_expr *query;
532+
PLpgSQL_expr *query; /* if static query */
533+
PLpgSQL_expr *dynquery; /* if dynamic query (RETURN QUERY EXECUTE) */
534+
List *params; /* USING arguments for dynamic query */
533535
} PLpgSQL_stmt_return_query;
534536

535537
typedef struct

‎src/test/regress/expected/plpgsql.out

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3267,3 +3267,21 @@ end;
32673267
$$ language plpgsql;
32683268
ERROR: cursor FOR loop must use a bound cursor variable
32693269
CONTEXT: compile of PL/pgSQL function "forc_bad" near line 4
3270+
-- return query execute
3271+
create or replace function return_dquery()
3272+
returns setof int as $$
3273+
begin
3274+
return query execute 'select * from (values(10),(20)) f';
3275+
return query execute 'select * from (values($1),($2)) f' using 40,50;
3276+
end;
3277+
$$ language plpgsql;
3278+
select * from return_dquery();
3279+
return_dquery
3280+
---------------
3281+
10
3282+
20
3283+
40
3284+
50
3285+
(4 rows)
3286+
3287+
drop function return_dquery();

‎src/test/regress/sql/plpgsql.sql

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2669,3 +2669,17 @@ begin
26692669
end loop;
26702670
end;
26712671
$$ language plpgsql;
2672+
2673+
-- return query execute
2674+
2675+
create or replace function return_dquery()
2676+
returns setof int as $$
2677+
begin
2678+
return query execute 'select * from (values(10),(20)) f';
2679+
return query execute 'select * from (values($1),($2)) f' using 40,50;
2680+
end;
2681+
$$ language plpgsql;
2682+
2683+
select * from return_dquery();
2684+
2685+
drop function return_dquery();

0 commit comments

Comments
 (0)