Creating a TYPE that is an Array within a Function (or a predefined Array TYPE) in PostgreSQL I am researching migration of a major system from Oracle to PostgreSQL.  Getting into coding functions now, and am looking to see if there is a compatible object in PostgreSQL where, dynamically within a Function I can create a TYPE as an array.

The syntax in Oracle is:

TYPE VAR_STRING IS VARRAY(10) VARCHAR2(30);


This creates a 10 element array TYPE named VAR_STRING to hold 10 strings of 30 characters.

I am playing with PG 9.6, and even in the Create Type dialog, it is not obvious to me to make a pre-defined TYPE that can be an array.
You can just use varchar(30)[]. Example:

DO
$$
DECLARE
   var_string varchar(30)[] := '{a,b,c,d,e,f,g,h,i,j}';
BEGIN
   RAISE NOTICE '%', var_string;
END
$$;


(Assigning at declaration time is optional; defaults to NULL.)
(Lower case identifiers are advisable for Postgres - in contrast to Oracle.)

The manual on array declaration.

Of course, the number of elements is not limited to 10 for the basic type. Do you even need that? And do you need to enforce a maximum string length of 30? Typically a plain text array text[] should do the job.

You can specify the size of the array:

varchar(30)[10]


But that's not effective beyond documentation. The manual:


  However, the current implementation ignores any supplied array size
  limits, i.e., the behavior is the same as for arrays of unspecified length.


To positively enforce a maximum of 10 elements, you could create a DOMAIN over the basic type with a CHECK constraint.

CREATE DOMAIN varchar30_arr_max_10_elem AS varchar(30)[]
CHECK(cardinality(VALUE) &lt;= 10);


Then use varchar30_arr_max_10_elem like any other type.

Related:


Issues with PL/pgSQL code


Aside: Why use the name var_string for an array? It's not a string.