PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

Started by Quan Zongliangabout 2 years ago16 messages

Quan Zongliang

quanzongliang@yeah.net

about 2 years ago

1 attachment(s)

Implement TODO item:
PL/pgSQL
Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

As a first step, deal only with [], such as
xxx.yyy%TYPE[]
xxx%TYPE[]

It can be extended to support multi-dimensional and complex syntax in
the future.

--
Quan Zongliang

Attachments:

plpgsql-typearr.patchtext/plain; charset=UTF-8; name=plpgsql-typearr.patchDownload

diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index 6a09bfdd67..3ff12b7af9 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -23,6 +23,7 @@
 #include "parser/scanner.h"
 #include "parser/scansup.h"
 #include "utils/builtins.h"
+#include "utils/syscache.h"
 
 #include "plpgsql.h"
 
@@ -76,6 +77,7 @@ static	PLpgSQL_expr	*read_sql_expression2(int until, int until2,
 											  int *endtoken);
 static	PLpgSQL_expr	*read_sql_stmt(void);
 static	PLpgSQL_type	*read_datatype(int tok);
+static	PLpgSQL_type	*read_datatype_array(PLpgSQL_type *elem_typ);
 static	PLpgSQL_stmt	*make_execsql_stmt(int firsttoken, int location);
 static	PLpgSQL_stmt_fetch *read_fetch_direction(void);
 static	void			 complete_direction(PLpgSQL_stmt_fetch *fetch,
@@ -2783,6 +2785,55 @@ read_sql_construct(int until,
 	return expr;
 }
 
+static PLpgSQL_type *
+read_datatype_array(PLpgSQL_type *elem_typ)
+{
+	int 			tok;
+	HeapTuple		type_tup = NULL;
+	Form_pg_type	type_frm;
+	Oid				arrtyp_oid;
+
+	Assert(elem_typ);
+	if (!OidIsValid(elem_typ->typoid))
+		return elem_typ;
+
+	tok = yylex();
+	/* Next token is not square bracket. */
+	if (tok != '[')
+	{
+		plpgsql_push_back_token(tok);
+
+		return elem_typ;
+	}
+
+	tok = yylex();
+	/* For now, deal only with []. */
+	if (tok != ']')
+	{
+		plpgsql_push_back_token('[');
+		plpgsql_push_back_token(tok);
+
+		return elem_typ;
+	}
+
+	type_tup = SearchSysCache1(TYPEOID,
+							  ObjectIdGetDatum(elem_typ->typoid));
+	if (!HeapTupleIsValid(type_tup))
+		return elem_typ;
+
+	type_frm = (Form_pg_type) GETSTRUCT(type_tup);
+	arrtyp_oid = type_frm->typarray;
+	ReleaseSysCache(type_tup);
+
+	if (OidIsValid(arrtyp_oid))
+		return plpgsql_build_datatype(arrtyp_oid,
+							elem_typ->atttypmod,
+							elem_typ->collation,
+							NULL);
+	else
+		return elem_typ;
+}
+
 static PLpgSQL_type *
 read_datatype(int tok)
 {
@@ -2818,7 +2869,9 @@ read_datatype(int tok)
 			{
 				result = plpgsql_parse_wordtype(dtname);
 				if (result)
-					return result;
+				{
+					return read_datatype_array(result);
+				}
 			}
 			else if (tok_is_keyword(tok, &yylval,
 									K_ROWTYPE, "rowtype"))
@@ -2842,7 +2895,9 @@ read_datatype(int tok)
 			{
 				result = plpgsql_parse_wordtype(dtname);
 				if (result)
-					return result;
+				{
+					return read_datatype_array(result);
+				}
 			}
 			else if (tok_is_keyword(tok, &yylval,
 									K_ROWTYPE, "rowtype"))
@@ -2866,7 +2921,9 @@ read_datatype(int tok)
 			{
 				result = plpgsql_parse_cwordtype(dtnames);
 				if (result)
-					return result;
+				{
+					return read_datatype_array(result);
+				}
 			}
 			else if (tok_is_keyword(tok, &yylval,
 									K_ROWTYPE, "rowtype"))
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index 272f5d2111..8db28c1122 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -5814,6 +5814,31 @@ SELECT * FROM list_partitioned_table() AS t;
  2
 (2 rows)
 
+CREATE OR REPLACE FUNCTION array_partitioned_table()
+RETURNS SETOF partitioned_table.a%TYPE AS $$
+DECLARE
+    i int;
+    row partitioned_table%ROWTYPE;
+    a_val partitioned_table.a%TYPE[];
+    b_val partitioned_table.a%TYPE;
+    c_val b_val%TYPE[];
+BEGIN
+    i := 1;
+    FOR row IN SELECT * FROM partitioned_table ORDER BY a LOOP
+        a_val[i] := row.a;
+        c_val[i] := a_val[i];
+        i = i + 1;
+    END LOOP;
+    RETURN QUERY SELECT unnest(c_val);
+END; $$ LANGUAGE plpgsql;
+NOTICE:  type reference partitioned_table.a%TYPE converted to integer
+SELECT * FROM array_partitioned_table() AS t;
+ t 
+---
+ 1
+ 2
+(2 rows)
+
 --
 -- Check argument name is used instead of $n in error message
 --
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index 924d524094..7b4df77d85 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -4748,6 +4748,26 @@ END; $$ LANGUAGE plpgsql;
 
 SELECT * FROM list_partitioned_table() AS t;
 
+CREATE OR REPLACE FUNCTION array_partitioned_table()
+RETURNS SETOF partitioned_table.a%TYPE AS $$
+DECLARE
+    i int;
+    row partitioned_table%ROWTYPE;
+    a_val partitioned_table.a%TYPE[];
+    b_val partitioned_table.a%TYPE;
+    c_val b_val%TYPE[];
+BEGIN
+    i := 1;
+    FOR row IN SELECT * FROM partitioned_table ORDER BY a LOOP
+        a_val[i] := row.a;
+        c_val[i] := a_val[i];
+        i = i + 1;
+    END LOOP;
+    RETURN QUERY SELECT unnest(c_val);
+END; $$ LANGUAGE plpgsql;
+
+SELECT * FROM array_partitioned_table() AS t;
+
 --
 -- Check argument name is used instead of $n in error message
 --

Daniel Gustafsson

daniel@yesql.se

about 2 years ago

In reply to: Quan Zongliang (#1)

Re: PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

On 16 Oct 2023, at 12:15, Quan Zongliang <quanzongliang@yeah.net> wrote:

Implement TODO item:
PL/pgSQL
Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

Cool! While I haven't looked at the patch yet, I've wanted this myself many
times in the past when working with large plpgsql codebases.

As a first step, deal only with [], such as
xxx.yyy%TYPE[]
xxx%TYPE[]

It can be extended to support multi-dimensional and complex syntax in the future.

Was this omitted due to complexity of implementation or for some other reason?

--
Daniel Gustafsson

Pavel Stehule

pavel.stehule@gmail.com

about 2 years ago

In reply to: Daniel Gustafsson (#2)

Re: PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

po 16. 10. 2023 v 13:56 odesílatel Daniel Gustafsson <daniel@yesql.se>
napsal:

On 16 Oct 2023, at 12:15, Quan Zongliang <quanzongliang@yeah.net> wrote:

Implement TODO item:
PL/pgSQL
Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

Cool! While I haven't looked at the patch yet, I've wanted this myself
many
times in the past when working with large plpgsql codebases.

As a first step, deal only with [], such as
xxx.yyy%TYPE[]
xxx%TYPE[]

It can be extended to support multi-dimensional and complex syntax in

the future.

Was this omitted due to complexity of implementation or for some other
reason?

There is no reason for describing enhancement. The size and dimensions of
postgresql arrays are dynamic, depends on the value, not on declaration.
Now, this information is ignored, and can be compatibility break to check
and enforce this info.

Show quoted text

--
Daniel Gustafsson

Quan Zongliang

quanzongliang@yeah.net

about 2 years ago

In reply to: Quan Zongliang (#1)

1 attachment(s)

Re: PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

Attached new patch
More explicit error messages based on type.

Show quoted text

On 2023/10/16 18:15, Quan Zongliang wrote:

Implement TODO item:
PL/pgSQL
Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

As a first step, deal only with [], such as
xxx.yyy%TYPE[]
xxx%TYPE[]

It can be extended to support multi-dimensional and complex syntax in
the future.

--
Quan Zongliang

Attachments:

plpgsql-typearr-v2.patchtext/plain; charset=UTF-8; name=plpgsql-typearr-v2.patchDownload

diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index 6a09bfdd67..c6377dcf2c 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -23,6 +23,7 @@
 #include "parser/scanner.h"
 #include "parser/scansup.h"
 #include "utils/builtins.h"
+#include "utils/syscache.h"
 
 #include "plpgsql.h"
 
@@ -76,6 +77,7 @@ static	PLpgSQL_expr	*read_sql_expression2(int until, int until2,
 											  int *endtoken);
 static	PLpgSQL_expr	*read_sql_stmt(void);
 static	PLpgSQL_type	*read_datatype(int tok);
+static	PLpgSQL_type	*read_datatype_array(PLpgSQL_type *elem_typ);
 static	PLpgSQL_stmt	*make_execsql_stmt(int firsttoken, int location);
 static	PLpgSQL_stmt_fetch *read_fetch_direction(void);
 static	void			 complete_direction(PLpgSQL_stmt_fetch *fetch,
@@ -2783,6 +2785,74 @@ read_sql_construct(int until,
 	return expr;
 }
 
+static PLpgSQL_type *
+read_datatype_array(PLpgSQL_type *elem_typ)
+{
+	int 			tok;
+	HeapTuple		type_tup = NULL;
+	Form_pg_type	type_frm;
+
+	Assert(elem_typ);
+	if (!OidIsValid(elem_typ->typoid))
+		return elem_typ;
+
+	tok = yylex();
+	/* Next token is not square bracket. */
+	if (tok != '[')
+	{
+		plpgsql_push_back_token(tok);
+
+		return elem_typ;
+	}
+
+	tok = yylex();
+	/* For now, deal only with []. */
+	if (tok != ']')
+	{
+		plpgsql_push_back_token(tok);
+		plpgsql_push_back_token('[');
+
+		return elem_typ;
+	}
+
+	type_tup = SearchSysCache1(TYPEOID,
+							  ObjectIdGetDatum(elem_typ->typoid));
+
+	/* should not happen. */
+	if (!HeapTupleIsValid(type_tup))
+		elog(ERROR, "cache lookup failed for type %u", elem_typ->typoid);
+
+	type_frm = (Form_pg_type) GETSTRUCT(type_tup);
+
+	if (OidIsValid(type_frm->typarray))
+	{
+		Oid arrtyp_oid = type_frm->typarray;
+
+		ReleaseSysCache(type_tup);
+
+		return plpgsql_build_datatype(arrtyp_oid,
+							elem_typ->atttypmod,
+							elem_typ->collation,
+							NULL);
+	}
+
+	if (type_frm->typcategory != TYPCATEGORY_ARRAY)
+		ereport(ERROR,
+				(errcode(ERRCODE_UNDEFINED_OBJECT),
+					errmsg("type \"%s[]\" does not exist",
+						NameStr(type_frm->typname)),
+					parser_errposition(yylloc)));
+
+	ereport(ERROR,
+			(errcode(ERRCODE_UNDEFINED_OBJECT),
+				errmsg("do not support multi-dimensional arrays in PL/pgSQL"),
+				parser_errposition(yylloc)));
+
+	ReleaseSysCache(type_tup);
+
+	return elem_typ;
+}
+
 static PLpgSQL_type *
 read_datatype(int tok)
 {
@@ -2818,7 +2888,9 @@ read_datatype(int tok)
 			{
 				result = plpgsql_parse_wordtype(dtname);
 				if (result)
-					return result;
+				{
+					return read_datatype_array(result);
+				}
 			}
 			else if (tok_is_keyword(tok, &yylval,
 									K_ROWTYPE, "rowtype"))
@@ -2842,7 +2914,9 @@ read_datatype(int tok)
 			{
 				result = plpgsql_parse_wordtype(dtname);
 				if (result)
-					return result;
+				{
+					return read_datatype_array(result);
+				}
 			}
 			else if (tok_is_keyword(tok, &yylval,
 									K_ROWTYPE, "rowtype"))
@@ -2866,7 +2940,9 @@ read_datatype(int tok)
 			{
 				result = plpgsql_parse_cwordtype(dtnames);
 				if (result)
-					return result;
+				{
+					return read_datatype_array(result);
+				}
 			}
 			else if (tok_is_keyword(tok, &yylval,
 									K_ROWTYPE, "rowtype"))
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index 272f5d2111..0e9497faaf 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -5814,6 +5814,53 @@ SELECT * FROM list_partitioned_table() AS t;
  2
 (2 rows)
 
+CREATE OR REPLACE FUNCTION array_partitioned_table()
+RETURNS SETOF partitioned_table.a%TYPE AS $$
+DECLARE
+    i int;
+    row partitioned_table%ROWTYPE;
+    a_val partitioned_table.a%TYPE[];
+    b_val partitioned_table.a%TYPE;
+    c_val b_val%TYPE[];
+BEGIN
+    i := 1;
+    FOR row IN SELECT * FROM partitioned_table ORDER BY a LOOP
+        a_val[i] := row.a;
+        c_val[i] := a_val[i];
+        i = i + 1;
+    END LOOP;
+    RETURN QUERY SELECT unnest(c_val);
+END; $$ LANGUAGE plpgsql;
+NOTICE:  type reference partitioned_table.a%TYPE converted to integer
+SELECT * FROM array_partitioned_table() AS t;
+ t 
+---
+ 1
+ 2
+(2 rows)
+
+CREATE OR REPLACE FUNCTION type_array_err1()
+RETURNS void AS $$
+DECLARE
+    i int[];
+    j i%TYPE[];
+BEGIN
+    RETURN;
+END; $$ LANGUAGE plpgsql;
+ERROR:  do not support multi-dimensional arrays in PL/pgSQL
+LINE 5:     j i%TYPE[];
+                     ^
+CREATE OR REPLACE FUNCTION type_array_err2()
+RETURNS void AS $$
+DECLARE
+    i pg_node_tree;
+    j i%TYPE[];
+BEGIN
+    RETURN;
+END; $$ LANGUAGE plpgsql;
+ERROR:  type "pg_node_tree[]" does not exist
+LINE 5:     j i%TYPE[];
+                     ^
 --
 -- Check argument name is used instead of $n in error message
 --
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index 924d524094..dfa491684a 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -4748,6 +4748,44 @@ END; $$ LANGUAGE plpgsql;
 
 SELECT * FROM list_partitioned_table() AS t;
 
+CREATE OR REPLACE FUNCTION array_partitioned_table()
+RETURNS SETOF partitioned_table.a%TYPE AS $$
+DECLARE
+    i int;
+    row partitioned_table%ROWTYPE;
+    a_val partitioned_table.a%TYPE[];
+    b_val partitioned_table.a%TYPE;
+    c_val b_val%TYPE[];
+BEGIN
+    i := 1;
+    FOR row IN SELECT * FROM partitioned_table ORDER BY a LOOP
+        a_val[i] := row.a;
+        c_val[i] := a_val[i];
+        i = i + 1;
+    END LOOP;
+    RETURN QUERY SELECT unnest(c_val);
+END; $$ LANGUAGE plpgsql;
+
+SELECT * FROM array_partitioned_table() AS t;
+
+CREATE OR REPLACE FUNCTION type_array_err1()
+RETURNS void AS $$
+DECLARE
+    i int[];
+    j i%TYPE[];
+BEGIN
+    RETURN;
+END; $$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION type_array_err2()
+RETURNS void AS $$
+DECLARE
+    i pg_node_tree;
+    j i%TYPE[];
+BEGIN
+    RETURN;
+END; $$ LANGUAGE plpgsql;
+
 --
 -- Check argument name is used instead of $n in error message
 --

Quan Zongliang

quanzongliang@yeah.net

about 2 years ago

In reply to: Quan Zongliang (#4)

Re: PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

Error messages still seem ambiguous.
do not support multi-dimensional arrays in PL/pgSQL

Isn't that better?
do not support multi-dimensional %TYPE arrays in PL/pgSQL

Show quoted text

On 2023/10/17 09:19, Quan Zongliang wrote:

Attached new patch
More explicit error messages based on type.

On 2023/10/16 18:15, Quan Zongliang wrote:

Implement TODO item:
PL/pgSQL
Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

As a first step, deal only with [], such as
xxx.yyy%TYPE[]
xxx%TYPE[]

It can be extended to support multi-dimensional and complex syntax in
the future.

--
Quan Zongliang

Quan Zongliang

quanzongliang@yeah.net

about 2 years ago

In reply to: Pavel Stehule (#3)

Re: PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

On 2023/10/16 20:05, Pavel Stehule wrote:

po 16. 10. 2023 v 13:56 odesílatel Daniel Gustafsson <daniel@yesql.se
<mailto:daniel@yesql.se>> napsal:

On 16 Oct 2023, at 12:15, Quan Zongliang <quanzongliang@yeah.net

<mailto:quanzongliang@yeah.net>> wrote:

Implement TODO item:
PL/pgSQL
Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

Cool! While I haven't looked at the patch yet, I've wanted this
myself many
times in the past when working with large plpgsql codebases.

As a first step, deal only with [], such as
xxx.yyy%TYPE[]
xxx%TYPE[]

It can be extended to support multi-dimensional and complex

syntax in the future.

Was this omitted due to complexity of implementation or for some
other reason?

Because of complexity.

There is no reason for describing enhancement. The size and dimensions
of postgresql arrays are dynamic, depends on the value, not on
declaration. Now, this information is ignored, and can be compatibility
break to check and enforce this info.

Yes. I don't think it's necessary.
If anyone needs it, we can continue to enhance it in the future.

Show quoted text

--
Daniel Gustafsson

Pavel Stehule

pavel.stehule@gmail.com

about 2 years ago

In reply to: Quan Zongliang (#6)

Re: PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

út 17. 10. 2023 v 3:30 odesílatel Quan Zongliang <quanzongliang@yeah.net>
napsal:

On 2023/10/16 20:05, Pavel Stehule wrote:

po 16. 10. 2023 v 13:56 odesílatel Daniel Gustafsson <daniel@yesql.se
<mailto:daniel@yesql.se>> napsal:

On 16 Oct 2023, at 12:15, Quan Zongliang <quanzongliang@yeah.net

<mailto:quanzongliang@yeah.net>> wrote:

Implement TODO item:
PL/pgSQL
Incomplete item Allow handling of %TYPE arrays, e.g.

tab.col%TYPE[]

Cool! While I haven't looked at the patch yet, I've wanted this
myself many
times in the past when working with large plpgsql codebases.

As a first step, deal only with [], such as
xxx.yyy%TYPE[]
xxx%TYPE[]

It can be extended to support multi-dimensional and complex

syntax in the future.

Was this omitted due to complexity of implementation or for some
other reason?

Because of complexity.

There is no reason for describing enhancement. The size and dimensions
of postgresql arrays are dynamic, depends on the value, not on
declaration. Now, this information is ignored, and can be compatibility
break to check and enforce this info.

Yes. I don't think it's necessary.
If anyone needs it, we can continue to enhance it in the future.

I don't think it is possible to do it. But there is another missing
functionality, if I remember well. There is no possibility to declare
variables for elements of array.

I propose syntax xxx.yyy%ELEMENTTYPE and xxx%ELEMENTTYPE

What do you think about it?

Regards

Pavel

Show quoted text

--
Daniel Gustafsson

Quan Zongliang

quanzongliang@yeah.net

about 2 years ago

In reply to: Pavel Stehule (#7)

Re: PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

On 2023/10/17 12:15, Pavel Stehule wrote:

út 17. 10. 2023 v 3:30 odesílatel Quan Zongliang <quanzongliang@yeah.net
<mailto:quanzongliang@yeah.net>> napsal:

On 2023/10/16 20:05, Pavel Stehule wrote:

po 16. 10. 2023 v 13:56 odesílatel Daniel Gustafsson

<daniel@yesql.se <mailto:daniel@yesql.se>

<mailto:daniel@yesql.se <mailto:daniel@yesql.se>>> napsal:

> On 16 Oct 2023, at 12:15, Quan Zongliang

<quanzongliang@yeah.net <mailto:quanzongliang@yeah.net>

<mailto:quanzongliang@yeah.net

<mailto:quanzongliang@yeah.net>>> wrote:

> Implement TODO item:
> PL/pgSQL
> Incomplete item Allow handling of %TYPE arrays, e.g.

tab.col%TYPE[]

Cool! While I haven't looked at the patch yet, I've wanted this
myself many
times in the past when working with large plpgsql codebases.

> As a first step, deal only with [], such as
> xxx.yyy%TYPE[]
> xxx%TYPE[]
>
> It can be extended to support multi-dimensional and complex
syntax in the future.

Was this omitted due to complexity of implementation or for some
other reason?

Because of complexity.

There is no reason for describing enhancement. The size and

dimensions

of postgresql arrays are dynamic, depends on the value, not on
declaration. Now, this information is ignored, and can be

compatibility

break to check and enforce this info.

Yes. I don't think it's necessary.
If anyone needs it, we can continue to enhance it in the future.

I don't think it is possible to do it. But there is another missing
functionality, if I remember well. There is no possibility to declare
variables for elements of array.

The way it's done now is more like laziness.

Is it possible to do that?
If the parser encounters %TYPE[][]. It can be parsed. Then let
parse_datatype do the rest.

For example, partitioned_table.a%TYPE[][100][]. Parse the type
name(int4) of partitioned_table.a%TYPE and add the following [][100][].
Passing "int4[][100][]" to parse_datatype will give us the array
definition we want.

Isn't this code a little ugly?

I propose syntax xxx.yyy%ELEMENTTYPE and xxx%ELEMENTTYPE

What do you think about it?

No other relational database can be found with such an implementation.
But it seems like a good idea. It can bring more convenience to write
stored procedure.

Show quoted text

Regards

Pavel

--
Daniel Gustafsson

Pavel Stehule

pavel.stehule@gmail.com

about 2 years ago

In reply to: Quan Zongliang (#8)

Re: PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

Isn't this code a little ugly?

I propose syntax xxx.yyy%ELEMENTTYPE and xxx%ELEMENTTYPE

What do you think about it?

No other relational database can be found with such an implementation.
But it seems like a good idea. It can bring more convenience to write
stored procedure.

No other databases support arrays :-)

Regards

Pavel

Show quoted text

Regards

Pavel

--
Daniel Gustafsson

#10

Pavel Stehule

pavel.stehule@gmail.com

about 2 years ago

In reply to: Quan Zongliang (#4)

1 attachment(s)

Re: PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

út 17. 10. 2023 v 3:20 odesílatel Quan Zongliang <quanzongliang@yeah.net>
napsal:

Attached new patch
More explicit error messages based on type.

On 2023/10/16 18:15, Quan Zongliang wrote:

Implement TODO item:
PL/pgSQL
Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

As a first step, deal only with [], such as
xxx.yyy%TYPE[]
xxx%TYPE[]

It can be extended to support multi-dimensional and complex syntax in
the future.

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

- 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

- 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[]"

- missing documentation

- I don't like using the word "partitioned" in the regress test name
"partitioned_table". It is confusing

Regards

Pavel

Show quoted text

--
Quan Zongliang

Attachments:

plpgsql-parser-reftype-array.patchtext/x-patch; charset=US-ASCII; name=plpgsql-parser-reftype-array.patchDownload

commit 3418f61191fe4b377717451b929cefb8028d3718
Author: okbob@github.com <pavel.stehule@gmail.com>
Date:   Mon Nov 20 08:49:41 2023 +0100

    poc

diff --git a/src/pl/plpgsql/src/pl_comp.c b/src/pl/plpgsql/src/pl_comp.c
index a341cde2c1..83e91d82d5 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(dtype->typoid))
+		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..4784e8fd5c 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 [ icons ]. 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);

#11

Quan Zongliang

quanzongliang@yeah.net

about 2 years ago

In reply to: Pavel Stehule (#10)

1 attachment(s)

Re: PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

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

Show quoted text

Regards

Pavel

Attachments:

plpgsql-typearr-v3.patchtext/plain; charset=UTF-8; name=plpgsql-typearr-v3.patchDownload

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;

#12

Pavel Stehule

pavel.stehule@gmail.com

about 2 years ago

In reply to: Quan Zongliang (#11)

1 attachment(s)

Re: PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

čt 23. 11. 2023 v 13:28 odesílatel Quan Zongliang <quanzongliang@yeah.net>
napsal:

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

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

Regards

Pavel

Show quoted text

Regards

Pavel

Attachments:

v20231123-0001-support-of-syntax-type-and-rowtype.patchtext/x-patch; charset=US-ASCII; name=v20231123-0001-support-of-syntax-type-and-rowtype.patchDownload

From cb0af209ab82baa19ff916d4acccf30d3aec97b1 Mon Sep 17 00:00:00 2001
From: "okbob@github.com" <pavel.stehule@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 | 70 +++++++++++++++++++++++++++
 src/test/regress/sql/plpgsql.sql      | 64 ++++++++++++++++++++++++
 6 files changed, 245 insertions(+), 13 deletions(-)

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 5977534a62..d10cb31fe5 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[];
+user_id users.user_id%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;
+user_id users.user_id%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..b207014682 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -5825,3 +5825,73 @@ 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[]
+-- 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..0db50bc6fe 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -4756,3 +4756,67 @@ 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;
+$$;
+
+-- 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

#13

Quan Zongliang

quanzongliang@yeah.net

about 2 years ago

In reply to: Pavel Stehule (#12)

1 attachment(s)

Re: PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

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];

Fixed.

Show quoted text

Regards

Pavel

Regards

Pavel

Attachments:

v20231124-0001-support-of-syntax-type-and-rowtype.patchtext/plain; charset=UTF-8; name=v20231124-0001-support-of-syntax-type-and-rowtype.patchDownload

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 5977534a62..0eedf4a55d 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[];
+user_id 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;
+user_id 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..b207014682 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -5825,3 +5825,73 @@ 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[]
+-- 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..0db50bc6fe 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -4756,3 +4756,67 @@ 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;
+$$;
+
+-- 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;

#14

Pavel Stehule

pavel.stehule@gmail.com

about 2 years ago

In reply to: Quan Zongliang (#13)

1 attachment(s)

Re: PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

pá 24. 11. 2023 v 2:12 odesílatel Quan Zongliang <quanzongliang@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

Show quoted text

Fixed.

Regards

Pavel

Regards

Pavel

Attachments:

v20231124-0001-support-of-syntax-type-and-rowtype.patchtext/x-patch; charset=US-ASCII; name=v20231124-0001-support-of-syntax-type-and-rowtype.patchDownload

From 8fc6d02a8cea6cc897e4290ad7724b494e330ef8 Mon Sep 17 00:00:00 2001
From: "okbob@github.com" <pavel.stehule@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

#15

Tom Lane

tgl@sss.pgh.pa.us

about 2 years ago

In reply to: Pavel Stehule (#14)

1 attachment(s)

Re: PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

Pavel Stehule <pavel.stehule@gmail.com> writes:

Now, I think so this simple patch is ready for committers

I pushed this with some editorialization -- mostly, rewriting the
documentation and comments. I found that the existing docs for %TYPE
were not great. There are two separate use-cases, one for referencing
a table column and one for referencing a previously-declared variable,
and the docs were about as clear as mud about explaining that.

I also looked into the problem Pavel mentioned that it doesn't work
for RECORD. If you just write "record[]" you get an error message
that at least indicates it's an unsupported case:

regression=# do $$declare r record[]; begin end$$;
ERROR: variable "r" has pseudo-type record[]
CONTEXT: compilation of PL/pgSQL function "inline_code_block" near line 1

Maybe we could improve on that, but it would be a lot of work and
I'm not terribly excited about it. However, %TYPE fails entirely
for both "record" and named composite types, and the reason turns
out to be just that plpgsql_parse_wordtype fails to handle the
PLPGSQL_NSTYPE_REC case. So that's easily fixed.

I also wonder what the heck the last half of plpgsql_parse_wordtype
is for at all. It looks for a named type, which means you can do

regression=# do $$declare x float8%type; begin end$$;
DO

but that's just stupid. You could leave off the %TYPE and get
the same result. Moreover, it is inconsistent because
plpgsql_parse_cwordtype has no equivalent behavior:

regression=# do $$declare x pg_catalog.float8%type; begin end$$;
ERROR: syntax error at or near "%"
LINE 1: do $$declare x pg_catalog.float8%type; begin end$$;
^
CONTEXT: invalid type name "pg_catalog.float8%type"

It's also undocumented and untested (the code coverage report
shows this part is never reached). So I propose we remove it.

That leads me to the attached proposed follow-on patch.

Another thing we could think about, but I've not done it here,
is to make plpgsql_parse_wordtype and friends throw error
instead of just returning NULL when they don't find the name.
Right now, if NULL is returned, we end up passing the whole
string to parse_datatype, leading to unhelpful errors like
the one shown above. We could do better than that I think,
perhaps like "argument of %TYPE is not a known variable".

regards, tom lane

Attachments:

improve-handling-of-composites-in-%TYPE.patchtext/x-diff; charset=us-ascii; name=improve-handling-of-composites-in-%TYPE.patchDownload

diff --git a/src/pl/plpgsql/src/expected/plpgsql_record.out b/src/pl/plpgsql/src/expected/plpgsql_record.out
index afb922df29..36d65e4286 100644
--- a/src/pl/plpgsql/src/expected/plpgsql_record.out
+++ b/src/pl/plpgsql/src/expected/plpgsql_record.out
@@ -306,6 +306,32 @@ NOTICE:  r1 = (1,2)
 ERROR:  record "r1" has no field "nosuchfield"
 CONTEXT:  SQL expression "r1.nosuchfield"
 PL/pgSQL function inline_code_block line 9 at RAISE
+-- check that type record can be passed through %type
+do $$
+declare r1 record;
+        r2 r1%type;
+begin
+  r2 := row(1,2);
+  raise notice 'r2 = %', r2;
+  r2 := row(3,4,5);
+  raise notice 'r2 = %', r2;
+end$$;
+NOTICE:  r2 = (1,2)
+NOTICE:  r2 = (3,4,5)
+-- arrays of record are not supported at the moment
+do $$
+declare r1 record[];
+begin
+end$$;
+ERROR:  variable "r1" has pseudo-type record[]
+CONTEXT:  compilation of PL/pgSQL function "inline_code_block" near line 2
+do $$
+declare r1 record;
+        r2 r1%type[];
+begin
+end$$;
+ERROR:  variable "r2" has pseudo-type record[]
+CONTEXT:  compilation of PL/pgSQL function "inline_code_block" near line 3
 -- check repeated assignments to composite fields
 create table some_table (id int, data text);
 do $$
diff --git a/src/pl/plpgsql/src/pl_comp.c b/src/pl/plpgsql/src/pl_comp.c
index b745eaa3f8..c63719c796 100644
--- a/src/pl/plpgsql/src/pl_comp.c
+++ b/src/pl/plpgsql/src/pl_comp.c
@@ -1596,8 +1596,8 @@ plpgsql_parse_tripword(char *word1, char *word2, char *word3,
 
 
 /* ----------
- * plpgsql_parse_wordtype	The scanner found word%TYPE. word can be
- *				a variable name or a basetype.
+ * plpgsql_parse_wordtype	The scanner found word%TYPE. word should be
+ *				a pre-existing variable name.
  *
  * Returns datatype struct, or NULL if no match found for word.
  * ----------
@@ -1605,10 +1605,7 @@ plpgsql_parse_tripword(char *word1, char *word2, char *word3,
 PLpgSQL_type *
 plpgsql_parse_wordtype(char *ident)
 {
-	PLpgSQL_type *dtype;
 	PLpgSQL_nsitem *nse;
-	TypeName   *typeName;
-	HeapTuple	typeTup;
 
 	/*
 	 * Do a lookup in the current namespace stack
@@ -1623,39 +1620,13 @@ plpgsql_parse_wordtype(char *ident)
 		{
 			case PLPGSQL_NSTYPE_VAR:
 				return ((PLpgSQL_var *) (plpgsql_Datums[nse->itemno]))->datatype;
-
-				/* XXX perhaps allow REC/ROW here? */
-
+			case PLPGSQL_NSTYPE_REC:
+				return ((PLpgSQL_rec *) (plpgsql_Datums[nse->itemno]))->datatype;
 			default:
 				return NULL;
 		}
 	}
 
-	/*
-	 * Word wasn't found in the namespace stack. Try to find a data type with
-	 * that name, but ignore shell types and complex types.
-	 */
-	typeName = makeTypeName(ident);
-	typeTup = LookupTypeName(NULL, typeName, NULL, false);
-	if (typeTup)
-	{
-		Form_pg_type typeStruct = (Form_pg_type) GETSTRUCT(typeTup);
-
-		if (!typeStruct->typisdefined ||
-			typeStruct->typrelid != InvalidOid)
-		{
-			ReleaseSysCache(typeTup);
-			return NULL;
-		}
-
-		dtype = build_datatype(typeTup, -1,
-							   plpgsql_curr_compile->fn_input_collation,
-							   typeName);
-
-		ReleaseSysCache(typeTup);
-		return dtype;
-	}
-
 	/*
 	 * Nothing found - up to now it's a word without any special meaning for
 	 * us.
@@ -1689,8 +1660,8 @@ plpgsql_parse_cwordtype(List *idents)
 	{
 		/*
 		 * Do a lookup in the current namespace stack. We don't need to check
-		 * number of names matched, because we will only consider scalar
-		 * variables.
+		 * number of names matched, because field references aren't supported
+		 * here.
 		 */
 		nse = plpgsql_ns_lookup(plpgsql_ns_top(), false,
 								strVal(linitial(idents)),
@@ -1703,6 +1674,11 @@ plpgsql_parse_cwordtype(List *idents)
 			dtype = ((PLpgSQL_var *) (plpgsql_Datums[nse->itemno]))->datatype;
 			goto done;
 		}
+		else if (nse != NULL && nse->itemtype == PLPGSQL_NSTYPE_REC)
+		{
+			dtype = ((PLpgSQL_rec *) (plpgsql_Datums[nse->itemno]))->datatype;
+			goto done;
+		}
 
 		/*
 		 * First word could also be a table name
diff --git a/src/pl/plpgsql/src/sql/plpgsql_record.sql b/src/pl/plpgsql/src/sql/plpgsql_record.sql
index db655335b1..f0fd05ba48 100644
--- a/src/pl/plpgsql/src/sql/plpgsql_record.sql
+++ b/src/pl/plpgsql/src/sql/plpgsql_record.sql
@@ -199,6 +199,29 @@ begin
   raise notice 'r1.nosuchfield = %', r1.nosuchfield;
 end$$;
 
+-- check that type record can be passed through %type
+do $$
+declare r1 record;
+        r2 r1%type;
+begin
+  r2 := row(1,2);
+  raise notice 'r2 = %', r2;
+  r2 := row(3,4,5);
+  raise notice 'r2 = %', r2;
+end$$;
+
+-- arrays of record are not supported at the moment
+do $$
+declare r1 record[];
+begin
+end$$;
+
+do $$
+declare r1 record;
+        r2 r1%type[];
+begin
+end$$;
+
 -- check repeated assignments to composite fields
 create table some_table (id int, data text);

#16

Pavel Stehule

pavel.stehule@gmail.com

about 2 years ago

In reply to: Tom Lane (#15)

Re: PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

čt 4. 1. 2024 v 22:02 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

Pavel Stehule <pavel.stehule@gmail.com> writes:

Now, I think so this simple patch is ready for committers

I pushed this with some editorialization -- mostly, rewriting the
documentation and comments. I found that the existing docs for %TYPE
were not great. There are two separate use-cases, one for referencing
a table column and one for referencing a previously-declared variable,
and the docs were about as clear as mud about explaining that.

I also looked into the problem Pavel mentioned that it doesn't work
for RECORD. If you just write "record[]" you get an error message
that at least indicates it's an unsupported case:

regression=# do $$declare r record[]; begin end$$;
ERROR: variable "r" has pseudo-type record[]
CONTEXT: compilation of PL/pgSQL function "inline_code_block" near line 1

Maybe we could improve on that, but it would be a lot of work and
I'm not terribly excited about it. However, %TYPE fails entirely
for both "record" and named composite types, and the reason turns
out to be just that plpgsql_parse_wordtype fails to handle the
PLPGSQL_NSTYPE_REC case. So that's easily fixed.

I also wonder what the heck the last half of plpgsql_parse_wordtype
is for at all. It looks for a named type, which means you can do

regression=# do $$declare x float8%type; begin end$$;
DO

but that's just stupid. You could leave off the %TYPE and get
the same result. Moreover, it is inconsistent because
plpgsql_parse_cwordtype has no equivalent behavior:

regression=# do $$declare x pg_catalog.float8%type; begin end$$;
ERROR: syntax error at or near "%"
LINE 1: do $$declare x pg_catalog.float8%type; begin end$$;
^
CONTEXT: invalid type name "pg_catalog.float8%type"

It's also undocumented and untested (the code coverage report
shows this part is never reached). So I propose we remove it.

That leads me to the attached proposed follow-on patch.

Another thing we could think about, but I've not done it here,
is to make plpgsql_parse_wordtype and friends throw error
instead of just returning NULL when they don't find the name.
Right now, if NULL is returned, we end up passing the whole
string to parse_datatype, leading to unhelpful errors like
the one shown above. We could do better than that I think,
perhaps like "argument of %TYPE is not a known variable".

Regards

Pavel

Show quoted text

regards, tom lane