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

Commit4739159

Browse files
committed
Support RETURN QUERY EXECUTE in plpgsql.
Pavel Stehule
1 parent511b798 commit4739159

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+
inttok;
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+
intterm;
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
staticPreparedParamsData*exec_eval_using_params(PLpgSQL_execstate*estate,
198198
List*params);
199199
staticvoidfree_params_data(PreparedParamsData*ppd);
200+
staticPortalexec_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
staticint
28422855
exec_stmt_dynfors(PLpgSQL_execstate*estate,PLpgSQL_stmt_dynfors*stmt)
28432856
{
2844-
Datumquery;
2845-
boolisnull;
2846-
Oidrestype;
2847-
char*querystr;
28482857
Portalportal;
28492858
intrc;
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+
staticPortal
5181+
exec_dynquery_with_params(PLpgSQL_execstate*estate,PLpgSQL_expr*dynquery,
5182+
List*params)
5183+
{
5184+
Portalportal;
5185+
Datumquery;
5186+
boolisnull;
5187+
Oidrestype;
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+
returnportal;
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+
inti;
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
staticvoid

‎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
intcmd_type;
531531
intlineno;
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
typedefstruct

‎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 replacefunctionreturn_dquery()
2676+
returns setofintas $$
2677+
begin
2678+
return query execute'select * from (values(10),(20)) f';
2679+
return query execute'select * from (values($1),($2)) f' using40,50;
2680+
end;
2681+
$$ language plpgsql;
2682+
2683+
select*from return_dquery();
2684+
2685+
dropfunction return_dquery();

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp