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

Question

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

• myType (CREATE TYPE myType AS (c0 INT, c1 TEXT))
• SETOF myType
• RECORD
• SETOF RECORD

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.

Answer 1

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.