Postgresql – How to insert a record returned by a function into a table

insertplpgsqlpostgresqlset-returning-functions

I have a plpgsql function that takes one input parameter and returns multiple columns of data as a record.

CREATE FUNCTION my_func(
    IN id bigint,
    OUT foo bigint,
    OUT bar double precision
    )
  RETURNS RECORD AS
$BODY$
BEGIN
    -- some calculations
    foo = id + 1;
    bar = 42.0;
END;
$BODY$
  LANGUAGE plpgsql;

I want to execute my_func for each row of a table (table_input) with the id column as input parameter and insert the returned row into another table (table_output).
The table table_output has the same columns as the columns return by my_func.

How can I do that?

The query

SELECT my_func(id) INTO table_output FROM table_input;

gives me:

ERROR:  column "my_func" has pseudo-type record

The following query does what I want, but my_func() gets called twice per row:

INSERT INTO table_output 
SELECT
 (my_func(table_input.id)).foo,
 (my_func(table_input.id)).bar
FROM table_input;

Any suggestions?

Best Answer

Assuming current Postgres version 9.5, since it has not been defined.
Best solved with a LATERAL join (Postgres 9.3+).

The minimal working form:

INSERT INTO table_output
SELECT f.*
FROM   table_input t, my_func(t.id) f;

The same, more explicit:

INSERT INTO table_output (foo, bar)
SELECT f.foo, f.bar
FROM   table_input t
CROSS  JOIN LATERAL my_func(t.id) AS f;

Or, since exactly one result row is guaranteed in this case, a plain function call in a subquery does the same, probably cheaper:

INSERT INTO table_output
SELECT f.*
FROM  (SELECT my_func(id) AS f FROM table_input) t;

It's wise to declare target columns in a persisted INSERT, even if the row types of function and table currently match. This is more robust against later changes.

CROSS JOIN LATERAL is just the verbose equivalent of a simple comma before a set-returning function in the FROM list. (Binding more closely, but that's not relevant in this query.)

Detailed explanation for LATERAL and how to avoid repeated function evaluation in this recent, related answer on SO - with links to more:

Split function-returned record into multiple columns

Related Solutions

Postgresql – Error: set_valued function called in context that cannot accept a set. What is it about

The error message isn't very helpful:

regress=> SELECT * FROM  compute_all_pair_by_craig(100);
ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM  compute_all_pair_by_craig(100);

but if you rephrase the query to call it as a proper set-returning function you'll see the real problem:

regress=> SELECT * FROM compute_all_pair_by_craig(100);
ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM compute_all_pair_by_craig(100);

If you're using SETOF RECORD without an OUT parameter list you must specify the results in the calling statement, eg:

regress=> SELECT * FROM compute_all_pair_by_craig(100) theresult(a integer, b integer);

However, it's much better to use RETURNS TABLE or OUT parameters. With the former syntax your function would be:

create or replace function compute_all_pair_by_craig(id_obj bigint)
    returns table(a integer, b integer) as $$
begin
    return query select o.id, generate_series(0,o.value) from m_obj as o;     
end;
$$ language plpgsql;

This is callable in SELECT-list context and can be used without creating a type explicitly or specifying the result structure at the call site.

As for the second half of the question, what's happening is that the 1st case specifies two separate columns in a SELECT-list, wheras the second returns a single composite. It's actually not to do with how you're returning the result, but how you're invoking the function. If we create the sample function:

CREATE OR REPLACE FUNCTION twocols() RETURNS TABLE(a integer, b integer) 
AS $$ SELECT x, x FROM generate_series(1,5) x; $$ LANGUAGE sql;

You'll see the difference in the two ways to call a set-returning function - in the SELECT list, a PostgreSQL specific non-standard extension with quirky behaviour:

regress=> SELECT twocols();
 twocols 
---------
 (1,1)
 (2,2)
 (3,3)
 (4,4)
 (5,5)
(5 rows)

or as a table in the more standard way:

regress=> SELECT * FROM twocols();
 a | b 
---+---
 1 | 1
 2 | 2
 3 | 3
 4 | 4
 5 | 5
(5 rows)

PostgreSQL : send all rows of a table to a function

UPDATE mytbl
SET col2=myfn(col1)

should do the trick

Best Answer

Related Solutions

Postgresql – Error: set_valued function called in context that cannot accept a set. What is it about

PostgreSQL : send all rows of a table to a function

Related Question