On 2023/11/20 17:33, Pavel Stehule wrote:
I did some deeper check:
- I don't like too much parser's modification (I am sending alternative
own implementation) - the SQL parser allows richer syntax, and for full
functionality is only few lines more
Agree.
- original patch doesn't solve %ROWTYPE
(2023-11-20 10:04:36) postgres=# select * from foo;
┌────┬────┐
│ a │ b │
╞════╪════╡
│ 10 │ 20 │
│ 30 │ 40 │
└────┴────┘
(2 rows)
(2023-11-20 10:08:29) postgres=# do $$
declare v foo%rowtype[];
begin
v := array(select row(a,b) from foo);
raise notice '%', v;
end;
$$;
NOTICE: {"(10,20)","(30,40)"}
DO
two little fixes
1. spelling mistake
ARRAY [ icons ] --> ARRAY [ iconst ]
2. code bug
if (!OidIsValid(dtype->typoid)) --> if (!OidIsValid(array_typeid))
- original patch doesn't solve type RECORD
the error message should be more intuitive, although the arrays of
record type can be supported, but it probably needs bigger research.
(2023-11-20 10:10:34) postgres=# do $$
declare r record; v r%type[];
begin
v := array(select row(a,b) from foo);
raise notice '%', v;
end;
$$;
ERROR: syntax error at or near "%"
LINE 2: declare r record; v r%type[];
^
CONTEXT: invalid type name "r%type[]"
Currently only scalar variables are supported.
This error is consistent with the r%type error. And record arrays are
not currently supported.
Support for r%type should be considered first. For now, let r%type[]
report the same error as record[].
I prefer to implement it with a new patch.
- missing documentation
My English is not good. I wrote it down, please correct it. Add a note
in the "Record Types" documentation that arrays and "Copying Types" are
not supported yet.
- I don't like using the word "partitioned" in the regress test name
"partitioned_table". It is confusing
fixed
Regards
Pavel
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 5977534a62..ad02c9f561 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -766,6 +766,30 @@ SELECT merge_fields(t.*) FROM table1 t WHERE ... ;
</para>
</sect2>
+ <sect2 id="plpgsql-declaration-typearrays">
+ <title>Arrays of Copying Types and Row Types</title>
+
+<synopsis>
+<replaceable>name</replaceable> variable%TYPE[];
+<replaceable>name</replaceable> table_name%ROWTYPE[];
+</synopsis>
+
+ <para>
+ Arrays of Copying Types and Row Types is defined by appending square
brackets
+ (<literal>[]</literal>) to the <literal>%TYPE</literal> or
<literal>%ROWTYPE</literal>.
+ Its definition is similar to PostgreSQL's array types. It is possible to
+ specify the exact size of the array. The keyword ARRAY can also be used.
+ For example:
+<programlisting>
+user_id users.user_id%TYPE[4][2];
+user_id users.user_id%ROWTYPE ARRAY[4][];
+</programlisting>
+ However, the current implementation ignores any supplied array size
limits, i.e.,
+ the behavior is the same as for arrays of unspecified length.
+ The current implementation does not enforce the declared number of
dimensions either.
+ </para>
+ </sect2>
+
<sect2 id="plpgsql-declaration-records">
<title>Record Types</title>
@@ -794,6 +818,11 @@ SELECT merge_fields(t.*) FROM table1 t WHERE ... ;
calling query is parsed, whereas a record variable can change its row
structure on-the-fly.
</para>
+
+ <para>
+ <literal>RECORD</literal> does not support being defined as an array.
+ "Copying Types" as shown in <xref linkend="plpgsql-declaration-type"/> is
also not supported.
+ </para>
</sect2>
<sect2 id="plpgsql-declaration-collation">
diff --git a/src/pl/plpgsql/src/pl_comp.c b/src/pl/plpgsql/src/pl_comp.c
index a341cde2c1..a9cb15df6d 100644
--- a/src/pl/plpgsql/src/pl_comp.c
+++ b/src/pl/plpgsql/src/pl_comp.c
@@ -2095,6 +2095,29 @@ plpgsql_build_datatype(Oid typeOid, int32 typmod,
return typ;
}
+/*
+ * Returns an array for type specified as argument.
+ */
+PLpgSQL_type *
+plpgsql_datatype_arrayof(PLpgSQL_type *dtype)
+{
+ Oid array_typeid;
+
+ if (dtype->typisarray)
+ return dtype;
+
+ array_typeid = get_array_type(dtype->typoid);
+
+ if (!OidIsValid(array_typeid))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("could not find array type for data
type \"%s\"",
+
format_type_be(dtype->typoid))));
+
+ return plpgsql_build_datatype(array_typeid, dtype->atttypmod,
+
dtype->collation, NULL);
+}
+
/*
* Utility subroutine to make a PLpgSQL_type struct given a pg_type entry
* and additional details (see comments for plpgsql_build_datatype).
diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index 6a09bfdd67..7778bffefc 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -2789,7 +2789,7 @@ read_datatype(int tok)
StringInfoData ds;
char *type_name;
int startlocation;
- PLpgSQL_type *result;
+ PLpgSQL_type *result = NULL;
int parenlevel = 0;
/* Should only be called while parsing DECLARE sections */
@@ -2817,15 +2817,11 @@ read_datatype(int tok)
K_TYPE, "type"))
{
result = plpgsql_parse_wordtype(dtname);
- if (result)
- return result;
}
else if (tok_is_keyword(tok, &yylval,
K_ROWTYPE, "rowtype"))
{
result = plpgsql_parse_wordrowtype(dtname);
- if (result)
- return result;
}
}
}
@@ -2841,15 +2837,11 @@ read_datatype(int tok)
K_TYPE, "type"))
{
result = plpgsql_parse_wordtype(dtname);
- if (result)
- return result;
}
else if (tok_is_keyword(tok, &yylval,
K_ROWTYPE, "rowtype"))
{
result = plpgsql_parse_wordrowtype(dtname);
- if (result)
- return result;
}
}
}
@@ -2865,19 +2857,58 @@ read_datatype(int tok)
K_TYPE, "type"))
{
result = plpgsql_parse_cwordtype(dtnames);
- if (result)
- return result;
}
else if (tok_is_keyword(tok, &yylval,
K_ROWTYPE, "rowtype"))
{
result = plpgsql_parse_cwordrowtype(dtnames);
- if (result)
- return result;
}
}
}
+ /* Array declaration can follow, but we check it only for known type */
+ if (result)
+ {
+ bool be_array = false;
+
+ tok = yylex();
+
+ /*
+ * SQL syntax allows multiple [] [ iconst ], ARRAY, ARRAY [ ]
+ * or ARRAY [ iconst ]. Should we support all? It is not too
hard.
+ */
+ if (tok_is_keyword(tok, &yylval,
+ K_ARRAY, "array"))
+ {
+ be_array = true;
+ tok = yylex();
+ }
+
+ if (tok == '[')
+ {
+ be_array = true;
+
+ while (tok == '[')
+ {
+ tok = yylex();
+ if (tok == ICONST)
+ tok = yylex();
+
+ if (tok != ']')
+ yyerror("syntax error, expected \"]\"");
+
+ tok = yylex();
+ }
+ }
+
+ plpgsql_push_back_token(tok);
+
+ if (be_array)
+ result = plpgsql_datatype_arrayof(result);
+
+ return result;
+ }
+
while (tok != ';')
{
if (tok == 0)
diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h
index 9f0a912115..9da5e5b225 100644
--- a/src/pl/plpgsql/src/plpgsql.h
+++ b/src/pl/plpgsql/src/plpgsql.h
@@ -1249,6 +1249,7 @@ extern PLpgSQL_type *plpgsql_parse_cwordrowtype(List
*idents);
extern PGDLLEXPORT PLpgSQL_type *plpgsql_build_datatype(Oid typeOid, int32
typmod,
Oid collation,
TypeName *origtypname);
+extern PLpgSQL_type *plpgsql_datatype_arrayof(PLpgSQL_type *dtype);
extern PLpgSQL_variable *plpgsql_build_variable(const char *refname, int
lineno,
PLpgSQL_type *dtype,
bool add2namespace);
diff --git a/src/test/regress/expected/plpgsql.out
b/src/test/regress/expected/plpgsql.out
index 272f5d2111..329e26ef4c 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -5825,3 +5825,53 @@ END; $$ LANGUAGE plpgsql;
ERROR: "x" is not a scalar variable
LINE 3: GET DIAGNOSTICS x = ROW_COUNT;
^
+CREATE TABLE plpgsql_type_arr(a int, b text);
+INSERT INTO plpgsql_type_arr values (10, 'first b');
+INSERT INTO plpgsql_type_arr values (20, 'second b');
+do $$
+declare
+ i int;
+ h i%type ARRAY;
+ v plpgsql_type_arr%rowtype ARRAY[2][][][];
+begin
+ h[99] = 99;
+ v := array(select row(a,b) from plpgsql_type_arr);
+ v[3] = '(15, 25)';
+ raise notice '% %', h, v;
+end;
+$$;
+NOTICE: [99:99]={99} {"(10,\"first b\")","(20,\"second b\")","(15,\" 25\")"}
+do $$
+declare
+ r record;
+ v r%type[];
+begin
+ v := array(select row(a,b) from plpgsql_type_arr);
+ raise notice '%', v;
+end;
+$$;
+ERROR: syntax error at or near "%"
+LINE 4: v r%type[];
+ ^
+CONTEXT: invalid type name "r%type[]"
+do $$
+declare
+ i int[];
+ j i%TYPE[];
+begin
+ j[100] = 100;
+ raise notice '%', j;
+end;
+$$;
+NOTICE: [100:100]={100}
+do $$
+declare
+ i pg_node_tree;
+ j i%TYPE[];
+begin
+ raise notice '%', j;
+end;
+$$;
+ERROR: could not find array type for data type "pg_node_tree"
+CONTEXT: compilation of PL/pgSQL function "inline_code_block" near line 4
+DROP TABLE plpgsql_type_arr;
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index 924d524094..9ae88a3ea0 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -4756,3 +4756,52 @@ BEGIN
GET DIAGNOSTICS x = ROW_COUNT;
RETURN;
END; $$ LANGUAGE plpgsql;
+
+CREATE TABLE plpgsql_type_arr(a int, b text);
+
+INSERT INTO plpgsql_type_arr values (10, 'first b');
+INSERT INTO plpgsql_type_arr values (20, 'second b');
+
+do $$
+declare
+ i int;
+ h i%type ARRAY;
+ v plpgsql_type_arr%rowtype ARRAY[2][][][];
+begin
+ h[99] = 99;
+ v := array(select row(a,b) from plpgsql_type_arr);
+ v[3] = '(15, 25)';
+ raise notice '% %', h, v;
+end;
+$$;
+
+do $$
+declare
+ r record;
+ v r%type[];
+begin
+ v := array(select row(a,b) from plpgsql_type_arr);
+ raise notice '%', v;
+end;
+$$;
+
+do $$
+declare
+ i int[];
+ j i%TYPE[];
+begin
+ j[100] = 100;
+ raise notice '%', j;
+end;
+$$;
+
+do $$
+declare
+ i pg_node_tree;
+ j i%TYPE[];
+begin
+ raise notice '%', j;
+end;
+$$;
+
+DROP TABLE plpgsql_type_arr;
