Insert to table from set returning function with parameters

Question

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…

Accepted Answer

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 ...
```

Insert to table from set returning function with parameters

Solution

Problem