PL/pgSQL function to INSERT array of rows

Question

I want to have a function written in PL/pgSQL that takes as parameter something like "collection of row", I mean I need to pass a dictionary-like structure to the function.

Pseudocode:

function_name({key1:val1,key2:val2, key3:val3 [, ...] })

How to do that?

Answer 1

With modern day PostgreSQL you can simplify such a function.
Test setup:

CREATE TABLE tbl1 (id int, value text);

No need to create a type explicitly in this case (if the type is based on a table row), it is created for every table implicitly.
Function:

CREATE FUNCTION f_insert_rows_into_tbl1(tbl1[])
  RETURNS void
  LANGUAGE sql AS
$func$
INSERT INTO tbl1  -- rare case where it's safe to omit target columns
SELECT *
FROM   unnest($1) sub;
$func$;

Since the composite row type is the only column in the subquery, it will be decomposed in the outer SELECT automatically.

Since the input type is defined by the table row, this is one of the rare cases where it's safe to omit target columns for the INSERT command. In fact, it may even be advisable - to stay in sync with possible future changes to the table definition.

Call:

SELECT f_insert_rows_into_tbl1('{"(1,foo)","(2,bar)"}');

Note the input syntax for an array of rows! If you are uncertain about the correct syntax, see:

• Correct syntax for array of composite type

Answer 2

Visit this site http://postgres.cz/wiki/PostgreSQL_SQL_Tricks and search for Array to table. There may be other ways on newer versions, but I had this in my bookmarks from awhile back. Source from website:

CREATE OR REPLACE FUNCTION unpack(anyarray)
RETURNS SETOF anyelement AS $$ 
SELECT $1[i] 
   FROM generate_series(array_lower($1,1), 
                        array_upper($1,1)) g(i);
$$ LANGUAGE sql STRICT IMMUTABLE;

select unpack(array['a','b','c']);