Passing an array or record to a stored procedure in PostgreSQL

Question

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

Any ideas?

Answer 1

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 in my_type[]) returns text language plpgsql as $$
begin
  return arr::text;
end;$$;

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

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