PL/pgSQL - %TYPE and ARRAY

Question

Is it possible to use the %TYPE and array together?

CREATE FUNCTION role_update(
  IN id "role".role_id % TYPE,
  IN name   "role".role_name % TYPE,
  IN user_id_list "user".user_id % TYPE[],
  IN permission_id_list  INT[]
)

I got syntax error by this, but I don't want to duplicate any column type, so I want to use "user".user_id % TYPE instead of simply INT because then it is easier to modify any column type later.

Answer 1

As the manual explains here:

The type of a column is referenced by writing table_name.column_name%TYPE. Using this feature can sometimes help make a function independent of changes to the definition of a table.

The same functionality can be used in the RETURNS clause.

But there is no simple way to derive an array type from a referenced column, at least none that I would know of.

About modifying any column type later:
You are aware that this type of syntax is only a syntactical convenience to derive the type from a table column? Once created, there is no link whatsoever to the table or column involved.

It helps to keep a whole create script in sync. But id doesn't help with later changes to live objects in the database.

Related answer on dba.SE:

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