In plpgsql, allow %TYPE and %ROWTYPE to be followed by array decoration.

This provides the useful ability to declare a variable that is an array
of the type of some other variable or some table column.

Quan Zongliang, Pavel Stehule

Discussion: https://postgr.es/m/ec4523e1-9e7e-f3ef-f9ce-bafd680ad6f6@yeah.net

1 files changed, 26 insertions, 3 deletions

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 5977534a627..c2b9c6adb01 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -675,12 +675,14 @@ DECLARE
    <title>Copying Types</title>
 
 <synopsis>
-<replaceable>variable</replaceable>%TYPE
+<replaceable>name</replaceable> <replaceable>table</replaceable>.<replaceable>column</replaceable>%TYPE
+<replaceable>name</replaceable> <replaceable>variable</replaceable>%TYPE
 </synopsis>
 
    <para>
-    <literal>%TYPE</literal> provides the data type of a variable or
-    table column. You can use this to declare variables that will hold
+    <literal>%TYPE</literal> provides the data type of a table column
+    or a previously-declared <application>PL/pgSQL</application>
+    variable. You can use this to declare variables that will hold
     database values. For example, let's say you have a column named
     <literal>user_id</literal> in your <literal>users</literal>
     table. To declare a variable with the same data type as
@@ -691,6 +693,21 @@ user_id users.user_id%TYPE;
    </para>
 
    <para>
+    It is also possible to write array decoration
+    after <literal>%TYPE</literal>, thereby creating a variable that holds
+    an array of the referenced type:
+<programlisting>
+user_ids users.user_id%TYPE[];
+user_ids users.user_id%TYPE ARRAY[4];  -- equivalent to the above
+</programlisting>
+    Just as when declaring table columns that are arrays, it doesn't
+    matter whether you write multiple bracket pairs or specific array
+    dimensions: <productname>PostgreSQL</productname> treats all arrays of
+    a given element type as the same type, regardless of dimensionality.
+    (See <xref linkend="arrays-declaration"/>.)
+   </para>
+
+   <para>
     By using <literal>%TYPE</literal> you don't need to know the data
     type of the structure you are referencing, and most importantly,
     if the data type of the referenced item changes in the future (for
@@ -740,6 +757,12 @@ user_id users.user_id%TYPE;
    </para>
 
    <para>
+    As with <literal>%TYPE</literal>, <literal>%ROWTYPE</literal> can be
+    followed by array decoration to declare a variable that holds an array
+    of the referenced composite type.
+   </para>
+
+   <para>
     Parameters to a function can be
     composite types (complete table rows). In that case, the
     corresponding identifier <literal>$<replaceable>n</replaceable></literal> will be a row variable, and fields can