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.
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:
Using referenced types in function's parameters has no sense (in PostgreSQL), because its translated intermediately to actual types, and it is stored as actual types. Sorry, PostgreSQL doesn't support this functionality - something different is using referenced types inside function, where actual type is detected every first time execution in session.
