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

Commitc59887f

Browse files
author
Neil Conway
committed
Add support for an optional INTO clause to PL/PgSQL's EXECUTE command.
This allows the result of executing a SELECT to be assigned to a rowvariable, record variable, or list of scalars. Docs and regression testsupdated. Per Pavel Stehule, improvements and cleanup by Neil Conway.
1 parent0f011f6 commitc59887f

File tree

6 files changed

+169
-25
lines changed

6 files changed

+169
-25
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 26 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.69 2005/05/26 04:08:31 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.70 2005/06/07 02:47:15 neilc Exp $
33
-->
44

55
<chapter id="plpgsql">
@@ -1251,13 +1251,14 @@ NULL;
12511251
<command>EXECUTE</command> statement is provided:
12521252

12531253
<synopsis>
1254-
EXECUTE <replaceable class="command">command-string</replaceable>;
1254+
EXECUTE <replaceable class="command">command-string</replaceable> [ INTO <replaceable>target</replaceable> ];
12551255
</synopsis>
12561256

12571257
where <replaceable>command-string</replaceable> is an expression
1258-
yielding a string (of type
1259-
<type>text</type>) containing the command
1260-
to be executed. This string is fed literally to the SQL engine.
1258+
yielding a string (of type <type>text</type>) containing the
1259+
command to be executed and <replaceable>target</replaceable> is a
1260+
record variable, row variable, or a comma-separated list of
1261+
simple variables and record/row fields.
12611262
</para>
12621263

12631264
<para>
@@ -1276,16 +1277,22 @@ EXECUTE <replaceable class="command">command-string</replaceable>;
12761277
</para>
12771278

12781279
<para>
1279-
The results from <command>SELECT</command> commands are discarded
1280-
by <command>EXECUTE</command>, and <command>SELECT INTO</command>
1281-
is not currently supported within <command>EXECUTE</command>.
1282-
So there is no way to extract a result from a dynamically-created
1283-
<command>SELECT</command> using the plain <command>EXECUTE</command>
1284-
command. There are two other ways to do it, however: one is to use the
1285-
<command>FOR-IN-EXECUTE</>
1286-
loop form described in <xref linkend="plpgsql-records-iterating">,
1287-
and the other is to use a cursor with <command>OPEN-FOR-EXECUTE</>, as
1288-
described in <xref linkend="plpgsql-cursor-opening">.
1280+
The <literal>INTO</literal> clause specifies where the results of
1281+
a <command>SELECT</command> command should be assigned. If a row
1282+
or variable list is provided, it must exactly match the structure
1283+
of the results produced by the <command>SELECT</command> (when a
1284+
record variable is used, it will configure itself to match the
1285+
result's structure automatically). If multiple rows are returned,
1286+
only the first will be assigned to the <literal>INTO</literal>
1287+
variable. If no rows are returned, NULL is assigned to the
1288+
<literal>INTO</literal> variable. If no <literal>INTO</literal>
1289+
clause is specified, the results of a <command>SELECT</command>
1290+
command are discarded.
1291+
</para>
1292+
1293+
<para>
1294+
<command>SELECT INTO</command> is not currently supported within
1295+
<command>EXECUTE</command>.
12891296
</para>
12901297

12911298
<para>
@@ -1364,7 +1371,7 @@ EXECUTE 'UPDATE tbl SET '
13641371
command, which has the form:
13651372

13661373
<synopsis>
1367-
GET DIAGNOSTICS <replaceable>variable</replaceable> = <replaceable>item</replaceable> <optional> , ... </optional>;
1374+
GET DIAGNOSTICS <replaceable>variable</replaceable> = <replaceable>item</replaceable> <optional> , ... </optional>;
13681375
</synopsis>
13691376

13701377
This command allows retrieval of system status indicators. Each
@@ -2173,7 +2180,7 @@ SELECT merge_db (1, 'dennis');
21732180
Another way is to use the cursor declaration syntax,
21742181
which in general is:
21752182
<synopsis>
2176-
<replaceable>name</replaceable> CURSOR <optional> ( <replaceable>arguments</replaceable> ) </optional> FOR <replaceable>query</replaceable>;
2183+
<replaceable>name</replaceable> CURSOR <optional> ( <replaceable>arguments</replaceable> ) </optional> FOR <replaceable>query</replaceable>;
21772184
</synopsis>
21782185
(<literal>FOR</> may be replaced by <literal>IS</> for
21792186
<productname>Oracle</productname> compatibility.)
@@ -2218,7 +2225,7 @@ DECLARE
22182225
<title><command>OPEN FOR</command> <replaceable>query</replaceable></title>
22192226

22202227
<synopsis>
2221-
OPEN <replaceable>unbound_cursor</replaceable> FOR <replaceable>query</replaceable>;
2228+
OPEN <replaceable>unbound_cursor</replaceable> FOR <replaceable>query</replaceable>;
22222229
</synopsis>
22232230

22242231
<para>
@@ -3188,7 +3195,7 @@ DECLARE
31883195
func_body text;
31893196
func_cmd text;
31903197
BEGIN
3191-
func_body := 'BEGIN';
3198+
func_body := 'BEGIN';
31923199

31933200
-- Notice how we scan through the results of a query in a FOR loop
31943201
-- using the FOR &lt;record&gt; construct.

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

Lines changed: 46 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,7 @@
44
* procedural language
55
*
66
* IDENTIFICATION
7-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.72 2005/05/26 04:08:31 momjian Exp $
7+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.73 2005/06/07 02:47:16 neilc Exp $
88
*
99
* This software is copyrighted by Jan Wieck - Hamburg.
1010
*
@@ -1250,19 +1250,62 @@ stmt_execsql: execsql_start lno
12501250
}
12511251
;
12521252

1253-
stmt_dynexecute :K_EXECUTElnoexpr_until_semi
1253+
stmt_dynexecute :K_EXECUTElno
12541254
{
12551255
PLpgSQL_stmt_dynexecute *new;
1256+
PLpgSQL_expr *expr;
1257+
int endtoken;
1258+
1259+
expr = read_sql_construct(K_INTO,';',"INTO|;","SELECT",
1260+
true,true, &endtoken);
12561261

12571262
new = palloc(sizeof(PLpgSQL_stmt_dynexecute));
12581263
new->cmd_type = PLPGSQL_STMT_DYNEXECUTE;
12591264
new->lineno =$2;
1260-
new->query =$3;
1265+
new->query = expr;
1266+
1267+
new->rec =NULL;
1268+
new->row =NULL;
1269+
1270+
/*
1271+
* If we saw "INTO", look for an additional
1272+
* row or record var.
1273+
*/
1274+
if (endtoken == K_INTO)
1275+
{
1276+
switch (yylex())
1277+
{
1278+
case T_ROW:
1279+
check_assignable((PLpgSQL_datum *) yylval.row);
1280+
new->row = yylval.row;
1281+
break;
1282+
1283+
case T_RECORD:
1284+
check_assignable((PLpgSQL_datum *) yylval.row);
1285+
new->rec = yylval.rec;
1286+
break;
1287+
1288+
case T_SCALAR:
1289+
new->row = read_into_scalar_list(yytext, yylval.scalar);
1290+
break;
1291+
1292+
default:
1293+
plpgsql_error_lineno =$2;
1294+
ereport(ERROR,
1295+
(errcode(ERRCODE_SYNTAX_ERROR),
1296+
errmsg("syntax error at\"%s\"",
1297+
yytext),
1298+
errdetail("Expected record or row variable.")));
1299+
}
1300+
if (yylex() !=';')
1301+
yyerror("syntax error");
1302+
}
12611303

12621304
$$ = (PLpgSQL_stmt *)new;
12631305
}
12641306
;
12651307

1308+
12661309
stmt_open:K_OPENlnocursor_varptr
12671310
{
12681311
PLpgSQL_stmt_open *new;

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

Lines changed: 27 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
* procedural language
44
*
55
* IDENTIFICATION
6-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.141 2005/05/26 04:08:31 momjian Exp $
6+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.142 2005/06/07 02:47:17 neilc Exp $
77
*
88
* This software is copyrighted by Jan Wieck - Hamburg.
99
*
@@ -2202,6 +2202,13 @@ exec_stmt_dynexecute(PLpgSQL_execstate *estate,
22022202
Oidrestype;
22032203
char*querystr;
22042204
intexec_res;
2205+
PLpgSQL_rec*rec=NULL;
2206+
PLpgSQL_row*row=NULL;
2207+
2208+
if (stmt->rec!=NULL)
2209+
rec= (PLpgSQL_rec*) (estate->datums[stmt->rec->recno]);
2210+
elseif (stmt->row!=NULL)
2211+
row= (PLpgSQL_row*) (estate->datums[stmt->row->rowno]);
22052212

22062213
/*
22072214
* First we evaluate the string expression after the EXECUTE keyword.
@@ -2221,9 +2228,27 @@ exec_stmt_dynexecute(PLpgSQL_execstate *estate,
22212228
/*
22222229
* Call SPI_execute() without preparing a saved plan. The returncode can
22232230
* be any standard OK.Note that while a SELECT is allowed, its
2224-
* results will be discarded.
2231+
* results will be discarded unless an INTO clause is specified.
22252232
*/
22262233
exec_res=SPI_execute(querystr,estate->readonly_func,0);
2234+
2235+
/* Assign to INTO variable */
2236+
if (rec||row)
2237+
{
2238+
if (exec_res!=SPI_OK_SELECT)
2239+
ereport(ERROR,
2240+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2241+
errmsg("EXECUTE ... INTO is only for SELECT")));
2242+
else
2243+
{
2244+
if (SPI_processed==0)
2245+
exec_move_row(estate,rec,row,NULL,SPI_tuptable->tupdesc);
2246+
else
2247+
exec_move_row(estate,rec,row,
2248+
SPI_tuptable->vals[0],SPI_tuptable->tupdesc);
2249+
}
2250+
}
2251+
22272252
switch (exec_res)
22282253
{
22292254
caseSPI_OK_SELECT:

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

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
* procedural language
44
*
55
* IDENTIFICATION
6-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.60 2005/05/26 04:08:31 momjian Exp $
6+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.61 2005/06/07 02:47:18 neilc Exp $
77
*
88
* This software is copyrighted by Jan Wieck - Hamburg.
99
*
@@ -524,6 +524,8 @@ typedef struct
524524
{/* Dynamic SQL string to execute */
525525
intcmd_type;
526526
intlineno;
527+
PLpgSQL_rec*rec;/* INTO record or row variable */
528+
PLpgSQL_row*row;
527529
PLpgSQL_expr*query;
528530
}PLpgSQL_stmt_dynexecute;
529531

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

Lines changed: 35 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2380,3 +2380,38 @@ ERROR: control reached end of function without RETURN
23802380
CONTEXT: PL/pgSQL function "missing_return_expr"
23812381
drop function void_return_expr();
23822382
drop function missing_return_expr();
2383+
--
2384+
-- EXECUTE ... INTO test
2385+
--
2386+
create table eifoo (i integer, y integer);
2387+
create type eitype as (i integer, y integer);
2388+
create or replace function execute_into_test(varchar) returns record as $$
2389+
declare
2390+
_r record;
2391+
_rt eifoo%rowtype;
2392+
_v eitype;
2393+
i int;
2394+
j int;
2395+
k int;
2396+
begin
2397+
execute 'insert into '||$1||' values(10,15)';
2398+
execute 'select (row).* from (select row(10,1)::eifoo) s' into _r;
2399+
raise notice '% %', _r.i, _r.y;
2400+
execute 'select * from '||$1||' limit 1' into _rt;
2401+
raise notice '% %', _rt.i, _rt.y;
2402+
execute 'select *, 20 from '||$1||' limit 1' into i, j, k;
2403+
raise notice '% % %', i, j, k;
2404+
execute 'select 1,2' into _v;
2405+
return _v;
2406+
end; $$ language plpgsql;
2407+
select execute_into_test('eifoo');
2408+
NOTICE: 10 1
2409+
NOTICE: 10 15
2410+
NOTICE: 10 15 20
2411+
execute_into_test
2412+
-------------------
2413+
(1,2)
2414+
(1 row)
2415+
2416+
drop table eifoo cascade;
2417+
drop type eitype cascade;

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

Lines changed: 32 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2018,3 +2018,35 @@ select missing_return_expr();
20182018

20192019
dropfunction void_return_expr();
20202020
dropfunction missing_return_expr();
2021+
2022+
--
2023+
-- EXECUTE ... INTO test
2024+
--
2025+
2026+
createtableeifoo (iinteger, yinteger);
2027+
createtypeeitypeas (iinteger, yinteger);
2028+
2029+
create or replacefunctionexecute_into_test(varchar) returns recordas $$
2030+
declare
2031+
_r record;
2032+
_rt eifoo%rowtype;
2033+
_v eitype;
2034+
iint;
2035+
jint;
2036+
kint;
2037+
begin
2038+
execute'insert into'||$1||' values(10,15)';
2039+
execute'select (row).* from (select row(10,1)::eifoo) s' into _r;
2040+
raise notice'% %',_r.i,_r.y;
2041+
execute'select * from'||$1||' limit 1' into _rt;
2042+
raise notice'% %',_rt.i,_rt.y;
2043+
execute'select *, 20 from'||$1||' limit 1' into i, j, k;
2044+
raise notice'% % %', i, j, k;
2045+
execute'select 1,2' into _v;
2046+
return _v;
2047+
end; $$ language plpgsql;
2048+
2049+
select execute_into_test('eifoo');
2050+
2051+
droptable eifoo cascade;
2052+
droptype eitype cascade;

0 commit comments

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