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

Commit5e8674d

Browse files
committed
In plpgsql, allow %TYPE and %ROWTYPE to be followed by array decoration.
This provides the useful ability to declare a variable that is an arrayof the type of some other variable or some table column.Quan Zongliang, Pavel StehuleDiscussion:https://postgr.es/m/ec4523e1-9e7e-f3ef-f9ce-bafd680ad6f6@yeah.net
1 parent5d06e99 commit5e8674d

File tree

6 files changed

+261
-32
lines changed

6 files changed

+261
-32
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 26 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -675,12 +675,14 @@ DECLARE
675675
<title>Copying Types</title>
676676

677677
<synopsis>
678-
<replaceable>variable</replaceable>%TYPE
678+
<replaceable>name</replaceable> <replaceable>table</replaceable>.<replaceable>column</replaceable>%TYPE
679+
<replaceable>name</replaceable> <replaceable>variable</replaceable>%TYPE
679680
</synopsis>
680681

681682
<para>
682-
<literal>%TYPE</literal> provides the data type of a variable or
683-
table column. You can use this to declare variables that will hold
683+
<literal>%TYPE</literal> provides the data type of a table column
684+
or a previously-declared <application>PL/pgSQL</application>
685+
variable. You can use this to declare variables that will hold
684686
database values. For example, let's say you have a column named
685687
<literal>user_id</literal> in your <literal>users</literal>
686688
table. To declare a variable with the same data type as
@@ -690,6 +692,21 @@ user_id users.user_id%TYPE;
690692
</programlisting>
691693
</para>
692694

695+
<para>
696+
It is also possible to write array decoration
697+
after <literal>%TYPE</literal>, thereby creating a variable that holds
698+
an array of the referenced type:
699+
<programlisting>
700+
user_ids users.user_id%TYPE[];
701+
user_ids users.user_id%TYPE ARRAY[4]; -- equivalent to the above
702+
</programlisting>
703+
Just as when declaring table columns that are arrays, it doesn't
704+
matter whether you write multiple bracket pairs or specific array
705+
dimensions: <productname>PostgreSQL</productname> treats all arrays of
706+
a given element type as the same type, regardless of dimensionality.
707+
(See <xref linkend="arrays-declaration"/>.)
708+
</para>
709+
693710
<para>
694711
By using <literal>%TYPE</literal> you don't need to know the data
695712
type of the structure you are referencing, and most importantly,
@@ -739,6 +756,12 @@ user_id users.user_id%TYPE;
739756
<literal>%ROWTYPE</literal> is more portable.)
740757
</para>
741758

759+
<para>
760+
As with <literal>%TYPE</literal>, <literal>%ROWTYPE</literal> can be
761+
followed by array decoration to declare a variable that holds an array
762+
of the referenced composite type.
763+
</para>
764+
742765
<para>
743766
Parameters to a function can be
744767
composite types (complete table rows). In that case, the

‎src/pl/plpgsql/src/expected/plpgsql_array.out

Lines changed: 79 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -93,3 +93,82 @@ LINE 1: a.r[1] := 2
9393
^
9494
QUERY: a.r[1] := 2
9595
CONTEXT: PL/pgSQL function inline_code_block line 2 at assignment
96+
--
97+
-- test of %type[] and %rowtype[] syntax
98+
--
99+
-- check supported syntax
100+
do $$
101+
declare
102+
v int;
103+
v1 v%type;
104+
v2 v%type[];
105+
v3 v%type[1];
106+
v4 v%type[][];
107+
v5 v%type[1][3];
108+
v6 v%type array;
109+
v7 v%type array[];
110+
v8 v%type array[1];
111+
v9 v%type array[1][1];
112+
v10 pg_catalog.pg_class%rowtype[];
113+
begin
114+
raise notice '%', pg_typeof(v1);
115+
raise notice '%', pg_typeof(v2);
116+
raise notice '%', pg_typeof(v3);
117+
raise notice '%', pg_typeof(v4);
118+
raise notice '%', pg_typeof(v5);
119+
raise notice '%', pg_typeof(v6);
120+
raise notice '%', pg_typeof(v7);
121+
raise notice '%', pg_typeof(v8);
122+
raise notice '%', pg_typeof(v9);
123+
raise notice '%', pg_typeof(v10);
124+
end;
125+
$$;
126+
NOTICE: integer
127+
NOTICE: integer[]
128+
NOTICE: integer[]
129+
NOTICE: integer[]
130+
NOTICE: integer[]
131+
NOTICE: integer[]
132+
NOTICE: integer[]
133+
NOTICE: integer[]
134+
NOTICE: integer[]
135+
NOTICE: pg_class[]
136+
-- some types don't support arrays
137+
do $$
138+
declare
139+
v pg_node_tree;
140+
v1 v%type[];
141+
begin
142+
end;
143+
$$;
144+
ERROR: could not find array type for data type pg_node_tree
145+
CONTEXT: compilation of PL/pgSQL function "inline_code_block" near line 4
146+
-- check functionality
147+
do $$
148+
declare
149+
v1 int;
150+
v2 varchar;
151+
a1 v1%type[];
152+
a2 v2%type[];
153+
begin
154+
v1 := 10;
155+
v2 := 'Hi';
156+
a1 := array[v1,v1];
157+
a2 := array[v2,v2];
158+
raise notice '% %', a1, a2;
159+
end;
160+
$$;
161+
NOTICE: {10,10} {Hi,Hi}
162+
create table array_test_table(a int, b varchar);
163+
insert into array_test_table values(1, 'first'), (2, 'second');
164+
do $$
165+
declare tg array_test_table%rowtype[];
166+
begin
167+
tg := array(select array_test_table from array_test_table);
168+
raise notice '%', tg;
169+
tg := array(select row(a,b) from array_test_table);
170+
raise notice '%', tg;
171+
end;
172+
$$;
173+
NOTICE: {"(1,first)","(2,second)"}
174+
NOTICE: {"(1,first)","(2,second)"}

‎src/pl/plpgsql/src/pl_comp.c

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2208,6 +2208,33 @@ build_datatype(HeapTuple typeTup, int32 typmod,
22082208
returntyp;
22092209
}
22102210

2211+
/*
2212+
* Build an array type for the element type specified as argument.
2213+
*/
2214+
PLpgSQL_type*
2215+
plpgsql_build_datatype_arrayof(PLpgSQL_type*dtype)
2216+
{
2217+
Oidarray_typeid;
2218+
2219+
/*
2220+
* If it's already an array type, use it as-is: Postgres doesn't do nested
2221+
* arrays.
2222+
*/
2223+
if (dtype->typisarray)
2224+
returndtype;
2225+
2226+
array_typeid=get_array_type(dtype->typoid);
2227+
if (!OidIsValid(array_typeid))
2228+
ereport(ERROR,
2229+
(errcode(ERRCODE_UNDEFINED_OBJECT),
2230+
errmsg("could not find array type for data type %s",
2231+
format_type_be(dtype->typoid))));
2232+
2233+
/* Note we inherit typmod and collation, if any, from the element type */
2234+
returnplpgsql_build_datatype(array_typeid,dtype->atttypmod,
2235+
dtype->collation,NULL);
2236+
}
2237+
22112238
/*
22122239
*plpgsql_recognize_err_condition
22132240
*Check condition name and translate it to SQLSTATE.

‎src/pl/plpgsql/src/pl_gram.y

Lines changed: 57 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -757,8 +757,9 @@ decl_const:
757757
decl_datatype:
758758
{
759759
/*
760-
* If there's a lookahead token, read_datatype
761-
* should consume it.
760+
* If there's a lookahead token, read_datatype() will
761+
* consume it, and then we must tell bison to forget
762+
* it.
762763
*/
763764
$$ =read_datatype(yychar);
764765
yyclearin;
@@ -2783,13 +2784,17 @@ read_sql_construct(int until,
27832784
return expr;
27842785
}
27852786

2787+
/*
2788+
* Read a datatype declaration, consuming the current lookahead token if any.
2789+
* Returns a PLpgSQL_type struct.
2790+
*/
27862791
static PLpgSQL_type *
27872792
read_datatype(int tok)
27882793
{
27892794
StringInfoData ds;
27902795
char *type_name;
27912796
intstartlocation;
2792-
PLpgSQL_type *result;
2797+
PLpgSQL_type *result = NULL;
27932798
intparenlevel = 0;
27942799

27952800
/* Should only be called while parsing DECLARE sections*/
@@ -2799,11 +2804,15 @@ read_datatype(int tok)
27992804
if (tok == YYEMPTY)
28002805
tok = yylex();
28012806

2807+
/* The current token is the start of what we'll pass to parse_datatype*/
28022808
startlocation = yylloc;
28032809

28042810
/*
2805-
* If we have a simple or composite identifier, check for %TYPE
2806-
* and %ROWTYPE constructs.
2811+
* If we have a simple or composite identifier, check for %TYPE and
2812+
* %ROWTYPE constructs. (Note that if plpgsql_parse_wordtype et al fail
2813+
* to recognize the identifier, we'll fall through and pass the whole
2814+
* string to parse_datatype, which will assuredly give an unhelpful
2815+
* "syntax error". Should we try to give a more specific error?)
28072816
*/
28082817
if (tok == T_WORD)
28092818
{
@@ -2815,18 +2824,10 @@ read_datatype(int tok)
28152824
tok = yylex();
28162825
if (tok_is_keyword(tok, &yylval,
28172826
K_TYPE, "type"))
2818-
{
28192827
result = plpgsql_parse_wordtype(dtname);
2820-
if (result)
2821-
return result;
2822-
}
28232828
else if (tok_is_keyword(tok, &yylval,
28242829
K_ROWTYPE, "rowtype"))
2825-
{
28262830
result = plpgsql_parse_wordrowtype(dtname);
2827-
if (result)
2828-
return result;
2829-
}
28302831
}
28312832
}
28322833
else if (plpgsql_token_is_unreserved_keyword(tok))
@@ -2839,18 +2840,10 @@ read_datatype(int tok)
28392840
tok = yylex();
28402841
if (tok_is_keyword(tok, &yylval,
28412842
K_TYPE, "type"))
2842-
{
28432843
result = plpgsql_parse_wordtype(dtname);
2844-
if (result)
2845-
return result;
2846-
}
28472844
else if (tok_is_keyword(tok, &yylval,
28482845
K_ROWTYPE, "rowtype"))
2849-
{
28502846
result = plpgsql_parse_wordrowtype(dtname);
2851-
if (result)
2852-
return result;
2853-
}
28542847
}
28552848
}
28562849
else if (tok == T_CWORD)
@@ -2863,21 +2856,56 @@ read_datatype(int tok)
28632856
tok = yylex();
28642857
if (tok_is_keyword(tok, &yylval,
28652858
K_TYPE, "type"))
2866-
{
28672859
result = plpgsql_parse_cwordtype(dtnames);
2868-
if (result)
2869-
return result;
2870-
}
28712860
else if (tok_is_keyword(tok, &yylval,
28722861
K_ROWTYPE, "rowtype"))
2873-
{
28742862
result = plpgsql_parse_cwordrowtype(dtnames);
2875-
if (result)
2876-
return result;
2877-
}
28782863
}
28792864
}
28802865

2866+
/*
2867+
* If we recognized a %TYPE or %ROWTYPE construct, see if it is followed
2868+
* by array decoration: [ ARRAY ] [ '[' [ iconst ] ']' [ ... ] ]
2869+
*
2870+
* Like the core parser, we ignore the specific numbers and sizes of
2871+
* dimensions; arrays of different dimensionality are still the same type
2872+
* in Postgres.
2873+
*/
2874+
if (result)
2875+
{
2876+
boolis_array = false;
2877+
2878+
tok = yylex();
2879+
if (tok_is_keyword(tok, &yylval,
2880+
K_ARRAY, "array"))
2881+
{
2882+
is_array = true;
2883+
tok = yylex();
2884+
}
2885+
while (tok == '[')
2886+
{
2887+
is_array = true;
2888+
tok = yylex();
2889+
if (tok == ICONST)
2890+
tok = yylex();
2891+
if (tok != ']')
2892+
yyerror("syntax error, expected \"]\"");
2893+
tok = yylex();
2894+
}
2895+
plpgsql_push_back_token(tok);
2896+
2897+
if (is_array)
2898+
result = plpgsql_build_datatype_arrayof(result);
2899+
2900+
return result;
2901+
}
2902+
2903+
/*
2904+
* Not %TYPE or %ROWTYPE, so scan to the end of the datatype declaration,
2905+
* which could include typmod or array decoration. We are not very picky
2906+
* here, instead relying on parse_datatype to complain about garbage. But
2907+
* we must count parens to handle typmods within cursor_arg correctly.
2908+
*/
28812909
while (tok != ';')
28822910
{
28832911
if (tok == 0)

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

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1249,6 +1249,7 @@ extern PLpgSQL_type *plpgsql_parse_cwordrowtype(List *idents);
12491249
externPGDLLEXPORTPLpgSQL_type*plpgsql_build_datatype(OidtypeOid,int32typmod,
12501250
Oidcollation,
12511251
TypeName*origtypname);
1252+
externPLpgSQL_type*plpgsql_build_datatype_arrayof(PLpgSQL_type*dtype);
12521253
externPLpgSQL_variable*plpgsql_build_variable(constchar*refname,intlineno,
12531254
PLpgSQL_type*dtype,
12541255
booladd2namespace);

0 commit comments

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