sqlpostgresqlcomposite-types

Insert to table from set returning function with parameters


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.


Solution

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