Pass an array or record to a function in PostgreSQL?

Question

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

Jack Douglas · Accepted Answer · 2018-01-21 13:35:48Z

27

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 $$
begin
  return arr::text;
end;$$;

select my_function(array[row(1,2),row(3,4)]::my_type[]);

| my_function       |
| :---------------- |
| {"(1,2)","(3,4)"} |

dbfiddle here

answered May 18, 2011 at 6:55

Jack Douglas

40.6k16 gold badges106 silver badges181 bronze badges

1 Answer 1