CODEWITHSUNDEEP
sqlfunctionpostgresqlplpgsqlpostgresql-9.3
Jeff G
Jeff G

Reputation: 4677

Call a Postgres function that returns a record, passing in the result of a query

The problem is that I need to run a query (only known at run-time) to determine the input parameter to my function, which needs to return a result. For example, I want to write:

CREATE FUNCTION foo(inputKey INT) RETURNS TABLE (c0 INT, c1 TEXT) AS $$
    BEGIN
        -- Do something with inputKey to compute and return result
    END;
$$ LANGUAGE PLPGSQL;

These are the queries I have tried to date:

-- Doesn't even run (exception)
SELECT * FROM foo(SELECT foreignKey FROM someTable WHERE primaryKey = 5);

-- Runs, but returns a single column with all columns listed in it's value
SELECT foo(foreignKey) FROM someTable WHERE primaryKey = 5;

-- Same problem as previous
SELECT * FROM (SELECT foo(foreignKey) FROM someTable WHERE primaryKey = 5) AS a

I have also tried changing the result type to

All of the above exhibit the same behavior.

What is the syntax to get a multi-column result from calling a plpgsql function that returns a record, table, or setof records? This problem sounds really simple, but I have been unable to figure out the syntax for this after numerous internet searches. All of the questions I have found online use of the syntax SELECT * FROM function(), with absolutely no examples of someone passing an input parameter from another query.

Upvotes: 2

Views: 4519

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656291

What is the syntax to get a multi-column result from calling a plpgsql function that returns a record, table, or setof records?

That's actually two or three different questions.

  • table and setof record are closely related ("table" implies a well defined row type, while records can be well defined or anonymous, requiring different treatment).
    @Kirk provided a solution for a table function.

  • Functions returning a single record are a different case. Those allow more ways to call them. Like:

    SELECT foo(foreignkey).*  -- decomposing is simple for well-known types
FROM   sometable
WHERE  primarykey = 5;

The Postgres query planner has a weak spot there and evaluates the function multiple times this way. In Postgres 9.3 use a LATERAL join instead for expensive functions returning multiple columns:

    SELECT f.*
    FROM   sometable s
    LEFT   JOIN LATERAL foo(s.foreignkey) f ON true
    WHERE  s.primarykey = 5;

Details:

Upvotes: 5

Kirk Roybal
Kirk Roybal

Reputation: 17837

You almost got there:

SELECT * FROM foo((SELECT foreignKey FROM someTable WHERE primaryKey = 5));

Note the extra set of parenthesis to create a sub-query. The return values will be used as function parameters.

The results will be expanded after the function is called.

Upvotes: 2

Related Questions