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

Commit025ffe5

Browse files
committed
Allow PL/pgSQL FOR statement to return values to scalars as well as
records and row types.Pavel Stehule
1 parent18cbc7a commit025ffe5

File tree

4 files changed

+124
-23
lines changed

4 files changed

+124
-23
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 7 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.84 2006/02/05 02:47:53 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.85 2006/02/12 06:03:38 momjian Exp $
33
-->
44

55
<chapter id="plpgsql">
@@ -2008,11 +2008,13 @@ END LOOP;
20082008
accordingly. The syntax is:
20092009
<synopsis>
20102010
<optional> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </optional>
2011-
FOR <replaceable>record_or_row</replaceable> IN <replaceable>query</replaceable> LOOP
2011+
FOR <replaceable>target</replaceable> IN <replaceable>query</replaceable> LOOP
20122012
<replaceable>statements</replaceable>
20132013
END LOOP <optional> <replaceable>label</replaceable> </optional>;
20142014
</synopsis>
2015-
The record or row variable is successively assigned each row
2015+
<replaceable>Target</replaceable> is a record variable, row variable,
2016+
or a comma-separated list of simple variables and record/row fields
2017+
which is successively assigned each row
20162018
resulting from the <replaceable>query</replaceable> (which must be a
20172019
<command>SELECT</command> command) and the loop body is executed for each
20182020
row. Here is an example:
@@ -2047,7 +2049,7 @@ $$ LANGUAGE plpgsql;
20472049
rows:
20482050
<synopsis>
20492051
<optional> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </optional>
2050-
FOR <replaceable>record_or_row</replaceable> IN EXECUTE <replaceable>text_expression</replaceable> LOOP
2052+
FOR <replaceable>target</replaceable> IN EXECUTE <replaceable>text_expression</replaceable> LOOP
20512053
<replaceable>statements</replaceable>
20522054
END LOOP <optional> <replaceable>label</replaceable> </optional>;
20532055
</synopsis>
@@ -2067,7 +2069,7 @@ END LOOP <optional> <replaceable>label</replaceable> </optional>;
20672069
<literal>IN</> and <literal>LOOP</>. If <literal>..</> is not seen then
20682070
the loop is presumed to be a loop over rows. Mistyping the <literal>..</>
20692071
is thus likely to lead to a complaint along the lines of
2070-
<quote>loop variable of loop over rows must be a record or row variable</>,
2072+
<quote>loop variable of loop over rows must be a record or rowor scalarvariable</>,
20712073
rather than the simple syntax error one might expect to get.
20722074
</para>
20732075
</note>

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

Lines changed: 75 additions & 18 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.83 2006/02/1204:59:32 tgl Exp $
7+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.84 2006/02/1206:03:38 momjian Exp $
88
*
99
* This software is copyrighted by Jan Wieck - Hamburg.
1010
*
@@ -58,7 +58,9 @@ staticvoid check_sql_expr(const char *stmt);
5858
staticvoidplpgsql_sql_error_callback(void *arg);
5959
staticvoidcheck_labels(constchar *start_label,
6060
constchar *end_label);
61-
61+
static PLpgSQL_row *make_scalar_list1(constchar *name,
62+
PLpgSQL_datum *variable);
63+
6264
%}
6365

6466
%union {
@@ -76,6 +78,7 @@ staticvoid check_labels(const char *start_label,
7678
int lineno;
7779
PLpgSQL_rec *rec;
7880
PLpgSQL_row *row;
81+
PLpgSQL_datum *scalar;
7982
}forvariable;
8083
struct
8184
{
@@ -890,10 +893,15 @@ for_control:
890893
new->row =$2.row;
891894
check_assignable((PLpgSQL_datum *) new->row);
892895
}
896+
elseif ($2.scalar)
897+
{
898+
new->row = make_scalar_list1($2.name,$2.scalar);
899+
check_assignable((PLpgSQL_datum *) new->row);
900+
}
893901
else
894902
{
895903
plpgsql_error_lineno =$1;
896-
yyerror("loop variable of loop over rows must be a recordorrow variable");
904+
yyerror("loop variable of loop over rows must be a record, row,orscalar variable");
897905
}
898906
new->query = expr;
899907

@@ -948,6 +956,15 @@ for_control:
948956

949957
expr2 = plpgsql_read_expression(K_LOOP,"LOOP");
950958

959+
/* T_SCALAR identifier waits for converting*/
960+
if ($2.scalar)
961+
{
962+
char *name;
963+
plpgsql_convert_ident($2.name, &name,1);
964+
pfree($2.name);
965+
$2.name = name;
966+
}
967+
951968
/* create loop's private variable*/
952969
fvar = (PLpgSQL_var *)
953970
plpgsql_build_variable($2.name,
@@ -1002,10 +1019,15 @@ for_control:
10021019
new->row =$2.row;
10031020
check_assignable((PLpgSQL_datum *) new->row);
10041021
}
1022+
elseif ($2.scalar)
1023+
{
1024+
new->row = make_scalar_list1($2.name,$2.scalar);
1025+
check_assignable((PLpgSQL_datum *) new->row);
1026+
}
10051027
else
10061028
{
10071029
plpgsql_error_lineno =$1;
1008-
yyerror("loop variable of loop over rows must be recordorrow variable");
1030+
yyerror("loop variable of loop over rows must be record, row,orscalar variable");
10091031
}
10101032

10111033
new->query = expr1;
@@ -1027,14 +1049,31 @@ for_control:
10271049
* until we know what's what.
10281050
*/
10291051
for_variable:T_SCALAR
1030-
{
1052+
{
1053+
int tok;
10311054
char*name;
1055+
1056+
name = pstrdup(yytext);
1057+
$$.scalar = yylval.scalar;
1058+
$$.lineno = plpgsql_scanner_lineno();
10321059

1033-
plpgsql_convert_ident(yytext, &name,1);
1034-
$$.name = name;
1035-
$$.lineno = plpgsql_scanner_lineno();
1036-
$$.rec =NULL;
1037-
$$.row =NULL;
1060+
if((tok =yylex()) ==',')
1061+
{
1062+
plpgsql_push_back_token(tok);
1063+
$$.name =NULL;
1064+
$$.row = read_into_scalar_list(name,$$.scalar);
1065+
$$.rec =NULL;
1066+
$$.scalar =NULL;
1067+
1068+
pfree(name);
1069+
}
1070+
else
1071+
{
1072+
plpgsql_push_back_token(tok);
1073+
$$.name = name;
1074+
$$.row =NULL;
1075+
$$.rec =NULL;
1076+
}
10381077
}
10391078
|T_WORD
10401079
{
@@ -1048,20 +1087,14 @@ for_variable: T_SCALAR
10481087
}
10491088
|T_RECORD
10501089
{
1051-
char*name;
1052-
1053-
plpgsql_convert_ident(yytext, &name,1);
1054-
$$.name = name;
1090+
$$.name =NULL;
10551091
$$.lineno = plpgsql_scanner_lineno();
10561092
$$.rec = yylval.rec;
10571093
$$.row =NULL;
10581094
}
10591095
|T_ROW
10601096
{
1061-
char*name;
1062-
1063-
plpgsql_convert_ident(yytext, &name,1);
1064-
$$.name = name;
1097+
$$.name =NULL;
10651098
$$.lineno = plpgsql_scanner_lineno();
10661099
$$.row = yylval.row;
10671100
$$.rec =NULL;
@@ -2088,6 +2121,30 @@ make_fetch_stmt(void)
20882121
}
20892122

20902123

2124+
static PLpgSQL_row *
2125+
make_scalar_list1(constchar *name,
2126+
PLpgSQL_datum *variable)
2127+
{
2128+
PLpgSQL_row*row;
2129+
check_assignable(variable);
2130+
2131+
row =palloc(sizeof(PLpgSQL_row));
2132+
row->dtype = PLPGSQL_DTYPE_ROW;
2133+
row->refname =pstrdup("*internal*");
2134+
row->lineno =plpgsql_scanner_lineno();
2135+
row->rowtupdesc =NULL;
2136+
row->nfields =1;
2137+
row->fieldnames =palloc(sizeof(char *) *1);
2138+
row->varnos =palloc(sizeof(int) *1);
2139+
row->fieldnames[0] =pstrdup(name);
2140+
row->varnos[0] = variable->dno;
2141+
2142+
plpgsql_adddatum((PLpgSQL_datum *)row);
2143+
2144+
return row;
2145+
}
2146+
2147+
20912148
staticvoid
20922149
check_assignable(PLpgSQL_datum *datum)
20932150
{

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

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2721,3 +2721,23 @@ end;
27212721
$$ language plpgsql;
27222722
ERROR: end label "outer_label" specified for unlabelled block
27232723
CONTEXT: compile of PL/pgSQL function "end_label4" near line 5
2724+
-- using list of scalars in fori and fore stmts
2725+
create function for_vect() returns void as $$
2726+
<<lbl>>declare a integer; b varchar; c varchar; r record;
2727+
begin
2728+
-- old fori
2729+
for i in 1 .. 10 loop
2730+
raise notice '%', i;
2731+
end loop;
2732+
for a in select 1 from generate_series(1,4) loop
2733+
raise notice '%', a;
2734+
end loop;
2735+
for a,b,c in select generate_series, 'BB','CC' from generate_series(1,4) loop
2736+
raise notice '% % %', a, b, c;
2737+
end loop;
2738+
-- using qualified names in fors, fore is enabled, disabled only for fori
2739+
for lbl.a, lbl.b, lbl.c in execute E'select generate_series, \'bb\',\'cc\' from generate_series(1,4)' loop
2740+
raise notice '% % %', a, b, c;
2741+
end loop;
2742+
end;
2743+
$$ language plpgsql;

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

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2280,3 +2280,25 @@ begin
22802280
end loop outer_label;
22812281
end;
22822282
$$ language plpgsql;
2283+
2284+
2285+
-- using list of scalars in fori and fore stmts
2286+
createfunctionfor_vect() returns voidas $$
2287+
<<lbl>>declare ainteger; bvarchar; cvarchar; r record;
2288+
begin
2289+
-- old fori
2290+
for iin1 ..10 loop
2291+
raise notice'%', i;
2292+
end loop;
2293+
for ainselect1from generate_series(1,4) loop
2294+
raise notice'%', a;
2295+
end loop;
2296+
for a,b,cinselect generate_series,'BB','CC'from generate_series(1,4) loop
2297+
raise notice'% % %', a, b, c;
2298+
end loop;
2299+
-- using qualified names in fors, fore is enabled, disabled only for fori
2300+
forlbl.a,lbl.b,lbl.cin execute E'select generate_series,\'bb\',\'cc\' from generate_series(1,4)' loop
2301+
raise notice'% % %', a, b, c;
2302+
end loop;
2303+
end;
2304+
$$ language plpgsql;

0 commit comments

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