Calling set-returning function with each element in array

Question

I have a set-returning function (SRF) that accepts an integer argument and returns a set of rows from a table. I call it using SELECT * FROM tst.mySRF(3);, and then manipulate the returned value as if it were a table.

What I would like to do is to execute it on each element of an array; however, when I call it using SELECT * FROM tst.mySRF(unnest(array[3,4]));, an error is returned "set-valued function called in context that cannot accept a set". If I instead call it using SELECT tst.mySRF(unnest(array[3,4]));, I get a set of the type tst.tbl.

Table definition:

DROP TABLE tst.tbl CASCADE;
CREATE TABLE tst.tbl (
    id  serial  NOT NULL,
    txt text    NOT NULL,
    PRIMARY KEY (id)
);
INSERT INTO tst.tbl(txt) VALUES ('a'), ('b'), ('c'), ('d');

Function definition:

CREATE OR REPLACE FUNCTION tst.mySRF(
    IN  p_id            integer
)
    RETURNS setof tst.tbl
    LANGUAGE plpgsql
    AS $body$
        DECLARE
        BEGIN
            RETURN QUERY
                SELECT id, txt
                FROM tst.tbl
                WHERE id = p_id;
        END;
    $body$;

Calls:

SELECT * FROM tst.mySRF(3) returns a table, as expected.
SELECT tst.mySRF(unnest(array[3,4])) returns a table with a single column of the type tst.tbl, as expected.
SELECT * FROM tst.mySRF(unnest(array[3,4])) returns the error described above, I had expected a table.

Yes. What you wrote is correct. And what is your question? You can't run select * from function(function()) where inner function returns recordset. — user80168
– user80168, Commented Feb 6, 2013 at 16:11
The question would be how execute an a function for each element of an array and obtain a table, without using a series of UNION statements. — Matt
– Matt, Commented Feb 6, 2013 at 16:17
Well, you showed it - select function(other_function()). If you, for whatever reason, cannot do it like this, on 9.3 you can use LATERAL, and in previous version - use workaroud I described in here: depesz.com/2012/08/19/… — user80168
– user80168, Commented Feb 6, 2013 at 16:55
@depesz But the table returned by select function(other_function()) is a one column table of tst.tbl rows. How do I convert it to multi-column table of the type tst.tbl? Or does that require the 9.3 functionality you mentioned? — Matt
– Matt, Commented Feb 6, 2013 at 17:01

Daniel Vérité · Accepted Answer · 2013-02-06 17:33:04Z

3

To avoid the "table of single column" problem, you need to explicitly expand the SRF results with the (row).* notation

 SELECT (tst.mySRF(unnest(array[3,4]))).*;

If I understood @depesz, LATERAL will provide a more efficient or straightforward way to achieve the same result.

answered Feb 6, 2013 at 17:33

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Calling set-returning function with each element in array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related