Create plpgsql function that Inserts one row for each item in an array into table

Question

I need to create a plpgsql function that inserts one row for each item in an array into table.

For example: the_array = [1,2,3]

The result after inserting the_array into temp_table:

col
----
1
2
3

I found the following code from the this link: http://postgresql.1045698.n5.nabble.com/insert-into-table-from-list-or-array-td3217891.html

INSERT INTO tmptab 
   SELECT v 
     FROM unnest(string_to_array('1,2,4,2,1',',')) g(v) 

It works like a charm, but I do not understand the how this piece of code works. Could anyone explain it for me, please?

Answer 1

Function string_to_array('1,2,4,2,1', ',') creates an array from string, so you could use ARRAY[1,2,4,2,1] instead.

Function unnest(array) expands the array to a set of rows. So you have a set of five rows with integer in each of them.

The last part g(v) is needed because you need to name the set of rows and its column.

So the whole unnest(string_to_array('1,2,4,2,1',',')) returns a set of rows with one column in each. And it is mapped to g(v), so you can use v as the value.

If you had a table like create table g(v integer); and inserted there the rows with values: 1,2,4,2,1, then you would achieve the same result with:

INSERT INTO tmptab 
   SELECT v 
     FROM g; 

Answer 2

PostgreSQL supports Set Returning Function (second name is tabular function). Functions from this class returns a table instead scalar value. There are a lot of use cases - one use case is a function "unnest"

CREATE OR REPLACE FUNCTION simple_srf(int)
RETURNS SETOF int AS $$
BEGIN
  FOR i IN 1..$1
  LOOP
    RETURN NEXT i; -- push value to result
  END LOOP;
  RETURN; -- finish execution
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

postgres=# SELECT * FROM simple_srf(3);
 simple_srf 
------------
          1
          2
          3
(3 rows)

Function "string_to_array" parse string using separator to array.

postgres=# select string_to_array('aaa*bbb*ccc','*');
 string_to_array 
-----------------
 {aaa,bbb,ccc}
(1 row)

Unnest is simple function, that transforms a array to table. A source code of this function in plpgsql can be:

-- use PostgreSQL 8.4 syntax for simplicity
CREATE OR REPLACE FUNCTION unnest(anyarray)
RETURNS SETOF anyelement AS $$
BEGIN
  FOR i IN array_lower($1,1) .. array_upper($1,1)
  LOOP
    RETURN NEXT $1[i];
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;

"Unnest" is a polymorphic set returned function. PostgreSQL supports more builtin srf (tabular) function - e.g. generic_series or generate_subscripts

A older implementation of "unnest" function was:

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

-- or with generate_subscripts
CREATE OR REPLACE FUNCTION unnest(anyarray)
RETURNS anyelement AS $$
/*
 * g is a table alias
 * i is a column alias
 */
SELECT $1[i] FROM generate_subscripts($1,1) g(i)
$$ LANGUAGE sql;