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

    IN id bigint,
    OUT foo bigint,
    OUT bar double precision
    -- some calculations
    foo = id + 1;
    bar = 42.0;
  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 
FROM table_input;

Any suggestions?

  • INSERT INTO table_output SELECT * FROM myfunc(id)? Commented Jul 12, 2016 at 16:35
  • Yes, thank your very much for your detailed answer! Commented Jul 14, 2016 at 11:47

1 Answer 1


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
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
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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.