Call function where argument is a (sub)select statement

Question

My function takes an int4 as argument and returns a table:

SELECT * FROM test_function(545421); -- works fine
SELECT * FROM test_function(SELECT customerid
 FROM tableX where id = 1); -- syntax error

How can I make this work?
Server PostgreSQL 9.3.1

• user1822: I think you need an extra pair of parentheses: SELECT * FROM test_function((SELECT customerid FROM tableX where id = 1))

Answer 1

1. Subquery expression

You can fix it with parentheses like @a_horse commented:

SELECT * FROM test_function((SELECT customerid FROM tableX where id = 1));

But this form is error-prone. Nothing in the code guarantees that the subquery only returns a single row. We don't know whether id is unique and neither does Postgres (unless it looks up system catalogs). We have to rely on it and hope it won't break.

We could add LIMIT 1 to be sure, but then we probably should also add ORDER BY to get a deterministic pick ...

2. SRF function in the SELECT list

You can put the set-returning function call in the SELECT list:

SELECT test_function(customerid) FROM tablex WHERE id = 1;

But that has a number of implications:

1. Basics here:

   • What is the expected behaviour for multiple set-returning functions in SELECT clause?

2. Also works if the suquery returns multiple rows - and executes the function as many times, appending results to a single result set.

3. If the subquery returns no row, the function is not called at all - while the first syntax variant converts "no row" to a NULL value and calls the function with that input.

4. If the function returns a single column, that's decomposed automatically and you get a resultset with that column.

5. If the function returns more than one column, you still get a resultset with a single column, being a record. You can wrap that in a subquery and decompose in an outer SELECT:

   SELECT (f).* FROM (
    SELECT test_function(customerid) AS f FROM tablex WHERE id = 1
    ) sub;
   

   You could even decompose immediately:

   SELECT (test_function(customerid)).* FROM tablex WHERE id = 1;
   

   But that's the one form to avoid, because Postgres currently (Postgres 15) still has a weak spot there and ends up calling the function once per decomposed column. Needlessly expensive, and may have undesirable effects - think of volatile functions or side-effects nested in the function. See:

   • How to avoid multiple function evals with the (func()).* syntax in a query?

3. JOIN LATERAL

The modern, explicit syntax for this is a LATERAL join (since Postgres 9.3):

SELECT f.* 
FROM tableX t, test_function(t.customerid) f
WHERE t.id = 1;

Which is short syntax for:

SELECT f.* 
FROM tableX t
CROSS JOIN LATERAL test_function(t.customerid) f
WHERE t.id = 1;

Same result as with the first form (your fixed original), except for subtle but possibly important differences:

If tableX has no row for t.id = 1, the function in the LATERAL join is never called, and the query returns nothing (no row).
If, on the other hand, a row is found for t.id = 1 and that has customerid IS NULL, then the function is called with NULL input and we get whatever the function returns for NULL.
That's typically how it should be.

Your (fixed) original form with the subquery expression cannot distinguish between these two cases. "No row" results in the same NULL value as customerid being NULL. The function is called with NULL input either way.
That's typically not how it should be.

The LATERAL query also does not break if id is not unique. And you can easily return additional columns from tableX if you want.

So option 3. is clearly my favorite.

• Autumn Skye: One other benefit of CROSS JOIN LATERAL is that it handles invocations of the function over multiple rows of a result set, whereas the subquery expression can handle at most a single row, otherwise it will produce ERROR: more than one row returned by a subquery used as an expression. Also, the short form of the expression may produce ERROR: invalid reference to FROM-clause entry for table "t" if additional tables are joined.
• Randall: @ErwinBrandstetter - First, your answers are invaluable, and have helped me frequently - thank you. Second - in this question, I'm getting a little confused at the end with the "subtle but possibly important difference" - could you clarify which "form" is the reference in the two bullet points?
• Erwin Brandstetter: @Randall: Yeah, wasn't all that clear. I clarified a bit.