How to insert the output of a set-return function into a table
I have a function that takes two parameters, returns some data from tables. Want to insert the returned rows into a temporary table (with the same structure as the function's output) in another function.
Tried it 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;
Function RETURNS TABLE(val1 integer, val2 integer)
The choice doesn't 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 should I do?
source to share
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 (since your function is returning a table with two columns). The second returns rows with column one of the pseudo-type record.
You can use the query with
as in the question, but you have to place the function call in from
:
WITH x as (select * from function_name(p1, p2))
insert into tmp1 select * from x;
Of course the request
insert into tmp1 select * from function_name(p1, p2);
makes the same and easier.
source to share
Depending on your actual query, it may or may not make sense to extract the string 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 can be helpful to avoid evaluating a function multiple times.
- JOIN on setting return function results
- Combine postgres function with query
- Optimize GROUP BY query to get last record per user
But if you do, you need the correct syntax to further decompose the string type:
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 parentheses around (implicit column alias for row type) up to composite type access columns (row type) . (
f
)
And don't forget to include the list of columns for the target INSERT
in the saved statements. If you don't, it may break in unexpected ways if you later change the table layout tmp1
.
In this case, it is probably better to also target the corresponding function result columns explicitly by name ( (f).val1, (f).val2
) instead of(f).*
By the way, the fact is that your function taking two parameters
is irrelevant to the problem. Only the return type definition is used.
source to share