Array of template type in PL/pgSQL function using %TYPE

I have a PostgreSQL database and a PL/pgSQL function that currently has arguments with copied types (for example IN arg_1 table_1.column_1%TYPE).

I want to update it so one of those arguments is actually an array of elements with that copied type. If it were a "normal" type, I'd just add [] after the type, like IN arg_1 integer[]. Following that logic, I tried adding the square brackets right after %TYPE, but I get a syntax error.

I couldn't find anything about in the Postgres docs, nor did my google fu yield useful answers.

How can I achieve that?

The %TYPE construct can only copy the exact type. But there is a simple workaround to register the according array type: Create a (temporary) table or view with the desired array type.

Template table:

CREATE TABLE foo (i int);

(Temporary) view to register the array type, optionally empty (LIMIT 0):

CREATE TEMP VIEW v_foo_i_arr AS
SELECT ARRAY (SELECT i FROM foo LIMIT 0) AS i_arr;

Or a (temporary) table:

CREATE TEMP TABLE foo_i_arr AS
SELECT ARRAY (SELECT i FROM foo LIMIT 0) AS i_arr;

Now your function works:

CREATE OR REPLACE FUNCTION f_arr_test(v_foo_i_arr.i_arr%TYPE)
  RETURNS foo.i%TYPE AS  -- example: return element type
$func$
BEGIN
   RETURN $1[2];
END
$func$  LANGUAGE plpgsql;

You might as well make that a temporary view or table, since the type in the function signature is converted to the underlying type immediately. No persisted connection to the used template. So it's no problem that temp objects are dropped at the end of the session.
Polymorphic type

You might be better off with a polymorphic type to begin with. This works for any array type:

CREATE OR REPLACE FUNCTION f_arr_test_polymorphic(ANYARRAY)
  RETURNS ANYELEMENT AS  -- derived from input type
$func$
BEGIN
   RETURN $1[2];
END
$func$  LANGUAGE plpgsql;

SQL Fiddle.

Note that using a %TYPE declaration inside the function body actually works dynamically in that it looks up the type in the system catalog at the first call of every session.

Related question on SO:

• PL/pgSQL - %TYPE and ARRAY

StackExchange Database Administrators Q#130357, answer score: 3