5

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

  • 1
    I think you need an extra pair of parentheses: SELECT * FROM test_function((SELECT customerid FROM tableX where id = 1)) – a_horse_with_no_name Apr 15 '15 at 14:03
11

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 rather error-prone. Nothing in the code guarantees that the sub-select 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 deterministic results ...

2. JOIN LATERAL

The modern syntax for this (Postgres 9.3+) would be a LATERAL join:

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;

Exactly the same result as above, except for one subtle but possibly important difference:

    1. If tableX has no row for t.id = 1, the function is never called and the query returns nothing (no row). If, on the other hand, a row is found with customerid IS NULL, the function is called with NULL input and we get whatever the function returns for NULL.
      That's how it should be.
    1. The 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 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.

  • 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. – vallismortis Apr 5 '18 at 13:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.