How to insert the output of set-returning function into a table

Question

I have a function taking two parameters, returns some data from tables. Want to insert the returned rows to a temp table (with same structure as function output) in another function.

Tried like this:

CREATE TEMP TABLE tmp1 (col1 int,  col2 int) ON COMMIT DROP;

WITH x as (select function_name(p1, p2))
    insert into tmp1 select * from x; 

The function RETURNS TABLE(val1 integer, val2 integer)

The select does not work.

ERROR:  column "col1" is of type integer but expression is of type record
HINT:  You will need to rewrite or cast the expression.

What shall I do?

Answer 1

and this way?..

insert into tmp1 select * from function_name(p1, p2); 

Answer 2

There is an important difference between these two queries:

select * from function_name(1, 2);
select function_name(1, 2);

The first query returns rows with two columns (as your function returns table with two columns). The second one returns rows with one column of pseudo-type record.

You can use with query as in the question, but you should place the function call in from clause:

WITH x as (select * from function_name(p1, p2))
    insert into tmp1 select * from x; 

Of course the query

insert into tmp1 select * from function_name(p1, p2); 

does the same and is simpler.

Answer 3

Depending on your actual query it may or may not make sense to fetch a row type from the function (instead of decomposing the result with SELECT * FROM function_name(p1, p2)) in a CTE (or subquery). In some contexts it may be useful to avoid multiple evaluation of the function.

• JOIN on set returning function results
• Combine postgres function with query
• Optimize GROUP BY query to retrieve latest record per user

But if you do, you need the right syntax to decompose the row type column later:

WITH x AS (select function_name(p1, p2) f)
INSERT INTO tmp1(col1,  col2)  -- supply a column list
-- SELECT (f).*               -- note the parentheses ...
SELECT (f).val1, (f).val2   -- .... but better yet
FROM   x;

You need the parentheses around (f) (the implicit column alias for the row type) to access columns of a composite type (row type).

• SUM & GROUP BY on an array of composite type

And be sure to supply a column list for the target of the INSERT in persisted statements. If you don't this may break in surprising ways if you later change the table layout of tmp1.
In this case it is probably also better to target relevant columns of the function result explicitly by name ((f).val1, (f).val2) instead of (f).*

BTW, the fact that your function is taking two parameters is not relevant to the problem at hand. Only the definition of the return type matters.