Database Administration
postgresql functions array composite-types postgresql-9.0
Updated Sun, 21 Aug 2022 03:17:30 GMT

Pass an array or record to a function in PostgreSQL?

I have a task to pass arrays, records, and in some cases array of records as a parameter to functions in PostgreSQL.


Postgres has very flexible handling of arrays and composite types. This may be the sort of thing you are trying to do:

create type my_type as (val1 integer, val2 integer);
create function my_function(arr my_type[]) returns text language plpgsql as $$
  return arr::text;
select my_function(array[row(1,2),row(3,4)]::my_type[]);
| my_function       |
| :---------------- |
| {"(1,2)","(3,4)"} |

dbfiddle here