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?

Thanks for the answers. I am able to do it using "select * from function" .. !! — Raghu, Jun 26 '15 at 09:25

score 4 · Answer 1 · answered Jun 25 '15 at 08:34

4

and this way?..

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

answered Jun 25 '15 at 08:34

Vao Tsun

47,234
13
100
132

score 2 · Answer 2 · answered Jun 25 '15 at 09:46

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.

score 0 · Answer 3 · edited May 23 '17 at 11:59

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.

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.

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

3 Answers3