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

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.