pá 24. 11. 2023 v 2:12 odesílatel Quan Zongliang <quanzongli...@yeah.net>
napsal:
>
>
> On 2023/11/24 03:39, Pavel Stehule wrote:
>
> >
> > I modified the documentation a little bit - we don't need to extra
> > propose SQL array syntax, I think.
> > I rewrote regress tests - we don't need to test unsupported
> > functionality (related to RECORD).
> >
> > - all tests passed
> >
> I wrote two examples of errors:
> user_id users.user_id%ROWTYPE[];
> user_id users.user_id%ROWTYPE ARRAY[4][3];
>
there were more issues in this part - the name "user_id" is a bad name for
a composite variable. I renamed it.
+ I wrote a test related to usage type without array support.
Now, I think so this simple patch is ready for committers
Regards
Pavel
> Fixed.
>
> > Regards
> >
> > Pavel
> >
> >
> > >
> > > Regards
> > >
> > > Pavel
> >
From 8fc6d02a8cea6cc897e4290ad7724b494e330ef8 Mon Sep 17 00:00:00 2001
From: "ok...@github.com" <pavel.steh...@gmail.com>
Date: Thu, 23 Nov 2023 18:39:27 +0100
Subject: [PATCH] support of syntax %type[] and %rowtype[]
---
doc/src/sgml/plpgsql.sgml | 40 ++++++++++++++
src/pl/plpgsql/src/pl_comp.c | 23 ++++++++
src/pl/plpgsql/src/pl_gram.y | 60 +++++++++++++++-----
src/pl/plpgsql/src/plpgsql.h | 1 +
src/test/regress/expected/plpgsql.out | 80 +++++++++++++++++++++++++++
src/test/regress/sql/plpgsql.sql | 73 ++++++++++++++++++++++++
6 files changed, 264 insertions(+), 13 deletions(-)
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 5977534a62..aa848c034e 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -766,6 +766,40 @@ 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 arrays described in <xref linkend="arrays"/>.
+ For example:
+<programlisting>
+user_id users.user_id%TYPE[];
+users_row users%ROWTYPE[];
+</programlisting>
+ The syntax allows the exact size of arrays to be specified. However, the current
+ implementation ignores any supplied array size limits, i.e., the behavior is the
+ same as for arrays of unspecified length.
+ </para>
+
+ <para>
+ An alternative syntax, which conforms to the SQL standard by using the keyword
+ <literal>ARRAY</literal>, can be used for one-dimensional or multi-dimensional
+ arrays too:
+<programlisting>
+user_id users.user_id%TYPE ARRAY;
+users_row users%ROWTYPE ARRAY[4][3];
+</programlisting>
+ As before, however, PostgreSQL does not enforce the size restriction in any case.
+ </para>
+ </sect2>
+
<sect2 id="plpgsql-declaration-records">
<title>Record Types</title>
@@ -794,6 +828,12 @@ 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>
+ The <literal>RECORD</literal> cannot be used for declaration of variable
+ of an array type. "Copying Types" as shown in <xref linkend="plpgsql-declaration-type"/>
+ are not supported too.
+ </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..aa9103cf0e 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,61 @@ 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;
}
}
}
+ /*
+ * After %TYPE or %ROWTYPE syntax (the result type is known
+ * already), we should to check syntax of an array declaration.
+ * Supported syntax is same like SQL parser. Although array's
+ * dimensions and dimension's sizes can be specified, they are
+ * ignored.
+ */
+ if (result)
+ {
+ bool be_array = false;
+
+ tok = yylex();
+
+ /* Supported syntax: [ ARRAY ] [ '[' [ iconst ] ']' [ ... ] ] */
+ 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..3d7e800956 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -5825,3 +5825,83 @@ END; $$ LANGUAGE plpgsql;
ERROR: "x" is not a scalar variable
LINE 3: GET DIAGNOSTICS x = ROW_COUNT;
^
+--
+-- test of %type[] and %rowtype[] syntax
+--
+-- check supported syntax
+do $$
+declare
+ v int;
+ v1 v%type;
+ v2 v%type[];
+ v3 v%type[1];
+ v4 v%type[][];
+ v5 v%type[1][3];
+ v6 v%type array;
+ v7 v%type array[];
+ v8 v%type array[1];
+ v9 v%type array[1][1];
+ v10 pg_catalog.pg_class%rowtype[];
+begin
+ raise notice '%', pg_typeof(v1);
+ raise notice '%', pg_typeof(v2);
+ raise notice '%', pg_typeof(v3);
+ raise notice '%', pg_typeof(v4);
+ raise notice '%', pg_typeof(v5);
+ raise notice '%', pg_typeof(v6);
+ raise notice '%', pg_typeof(v7);
+ raise notice '%', pg_typeof(v8);
+ raise notice '%', pg_typeof(v9);
+ raise notice '%', pg_typeof(v10);
+end;
+$$;
+NOTICE: integer
+NOTICE: integer[]
+NOTICE: integer[]
+NOTICE: integer[]
+NOTICE: integer[]
+NOTICE: integer[]
+NOTICE: integer[]
+NOTICE: integer[]
+NOTICE: integer[]
+NOTICE: pg_class[]
+-- some types doesn't support arrays
+do $$
+declare
+ v pg_node_tree;
+ v1 v%type[];
+begin
+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
+-- check functionality
+do $$
+declare
+ v1 int;
+ v2 varchar;
+ a1 v1%type[];
+ a2 v2%type[];
+begin
+ v1 := 10;
+ v2 := 'Hi';
+ a1 := array[v1,v1];
+ a2 := array[v2,v2];
+ raise notice '% %', a1, a2;
+end;
+$$;
+NOTICE: {10,10} {Hi,Hi}
+create table plpgsql_test_table(a int, b varchar);
+insert into plpgsql_test_table values(1, 'first'), (2, 'second');
+do $$
+declare tg plpgsql_test_table%rowtype[];
+begin
+ tg := array(select plpgsql_test_table from plpgsql_test_table);
+ raise notice '%', tg;
+ tg := array(select row(a,b) from plpgsql_test_table);
+ raise notice '%', tg;
+end;
+$$;
+NOTICE: {"(1,first)","(2,second)"}
+NOTICE: {"(1,first)","(2,second)"}
+drop table plpgsql_test_table;
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index 924d524094..b76ad588b3 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -4756,3 +4756,76 @@ BEGIN
GET DIAGNOSTICS x = ROW_COUNT;
RETURN;
END; $$ LANGUAGE plpgsql;
+
+--
+-- test of %type[] and %rowtype[] syntax
+--
+
+-- check supported syntax
+do $$
+declare
+ v int;
+ v1 v%type;
+ v2 v%type[];
+ v3 v%type[1];
+ v4 v%type[][];
+ v5 v%type[1][3];
+ v6 v%type array;
+ v7 v%type array[];
+ v8 v%type array[1];
+ v9 v%type array[1][1];
+ v10 pg_catalog.pg_class%rowtype[];
+begin
+ raise notice '%', pg_typeof(v1);
+ raise notice '%', pg_typeof(v2);
+ raise notice '%', pg_typeof(v3);
+ raise notice '%', pg_typeof(v4);
+ raise notice '%', pg_typeof(v5);
+ raise notice '%', pg_typeof(v6);
+ raise notice '%', pg_typeof(v7);
+ raise notice '%', pg_typeof(v8);
+ raise notice '%', pg_typeof(v9);
+ raise notice '%', pg_typeof(v10);
+end;
+$$;
+
+-- some types doesn't support arrays
+do $$
+declare
+ v pg_node_tree;
+ v1 v%type[];
+begin
+end;
+$$;
+
+-- check functionality
+do $$
+declare
+ v1 int;
+ v2 varchar;
+ a1 v1%type[];
+ a2 v2%type[];
+begin
+ v1 := 10;
+ v2 := 'Hi';
+ a1 := array[v1,v1];
+ a2 := array[v2,v2];
+ raise notice '% %', a1, a2;
+end;
+$$;
+
+create table plpgsql_test_table(a int, b varchar);
+
+insert into plpgsql_test_table values(1, 'first'), (2, 'second');
+
+do $$
+declare tg plpgsql_test_table%rowtype[];
+begin
+ tg := array(select plpgsql_test_table from plpgsql_test_table);
+ raise notice '%', tg;
+ tg := array(select row(a,b) from plpgsql_test_table);
+ raise notice '%', tg;
+end;
+$$;
+
+drop table plpgsql_test_table;
--
2.42.0
