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
You can put the set-returning function call in the
SELECT test_function(customerid) FROM tablex WHERE id = 1;
But that has a number of implications:
Also works if the suquery returns multiple rows - and executes the function as many times, appending results to a single result set.
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.
If the function returns a single column, that's decomposed automatically and you get a resultset with that column.
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 (f).* FROM (
SELECT test_function(customerid) AS f FROM tablex WHERE id = 1
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:
The modern, explicit syntax for this is a
LATERAL join (since Postgres 9.3):
FROM tableX t, test_function(t.customerid) f
WHERE t.id = 1;
Which is short syntax for:
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:
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
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
NULL. The function is called with
NULL input either way.
That's typically not how it should be.
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.