PL/pgSQL - %TYPE and ARRAY
plpgsql, postgresql
Solution
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
Problem
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.