CODEWITHSUNDEEP
arrayspostgresqlplpgsql
biera
biera

Reputation: 2667

PL/pgSQL function to INSERT array of rows

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?

Upvotes: 2

Views: 6758

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656351

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:

Upvotes: 11

Tometzky
Tometzky

Reputation: 23880

I think you can use a composite type and array argument. Something like (untested):

create type keyvalue as (
  key text,
  value text
);

create function function_name(keyvalue[]) returns null as $$
declare
  keyvalue x;
begin
  foreach x in array $1 loop
    insert into tablename(key,value) values (x.key, x.value);
  end loop
end;
$$ language plpgsql;

This will not be a true dictionary, as you can add duplicate keyvalues and keyvalues with the same key and different value to this array. But just for passing arguments to a function it should be enough.

Using unpack() mentioned by JustBob will allow to not use a loop, which is not very SQL-y, but treat this array as a set.

Upvotes: 0

Kuberchaun
Kuberchaun

Reputation: 30314

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']);

Upvotes: 1

Related Questions