This is probably something simple but couldn't figure it out.
I've table Summary and function GetSummary
that returns row as set of Summary. I can query it like this
SELECT GetSummary(arg1, arg2)
GetSummary
-----------
(val1, val2, val3)
And like this that returns the actual columns:
SELECT * FROM GetSummary(arg1, arg2)
col1 | col2 | col3
------------------------
val1 | val2 | val3
Insertion to Summary works fine:
INSERT INTO Summary (SELECT * FROM GetSummary(arg1, arg2));
INSERT 0 1
But I can't figure out how to insert several rows at once based on columns in other table. I would like to do something like this:
INSERT INTO Summary (SELECT FROM GetSummary(OtherTable.x, OtherTable.y)
FROM OtherTable WHERE <some query>);
That fails because SELECT FROM GetSummary ..
doesn't return Summary table rows. The query SELECT * FROM GetSummary ..
would do that but then I don't know how to write the query.
Edit
Happened to stumble to solution few minutes after posting. The right syntax is
INSERT INTO Summary (SELECT (GetSummary(OtherTable.x, OtherTable.y)).*
FROM OtherTable WHERE <some query>);
The (X).* notation expands the select to columns.
The solution appended to the question still has syntax errors. It should be:
INSERT INTO Summary
SELECT (GetSummary(o.x, o.y)).*
FROM OtherTable o
WHERE <some condition>;
Must:
- Only one FROM
.
Optional:
- No parenthesis around the SELECT
needed.
- Table alias to simplify syntax.
The manual on Accessing Composite Types.
Also, it seems that your function is supposed to return one (or no) row. If that is the case, you should drop the SETOF
in the RETURNS
clause. Make that:
CREATE FUNCTION getsummary( ... ) RETURNS summary AS ...