Syntax error passing SQL result to PostgreSQL function accepting array

Question

I tried to pass the result of a SQL query to a function, but I got a syntax error.

contacts=> SELECT count(*) FROM update_name(contact_ids := select array(select id from contact where name is NULL));
ERROR:  syntax error at or near "select"
LINE 1: SELECT count(*) FROM update_name(contact_ids := select array...

The subselect returns BIGINTs, and the function accepts an array of BIGINTs. I verified that running the subselect and turning the result into an array of BIGINTs works.

Switching to positional notation did not make a difference. Using an Array constructor did not change anything, either.

Following an intuition, I wrapped the argument in parens:

SELECT count(*) FROM update_name(contact_ids := (select array(select id from contact where name is NULL)));

And that worked. I don't get why. The docs on expressions state that arguments in a function call are expressions. Function calls and Array constructors are expressions, so at least using the Array constructor should have worked.

Why do I need the parens? Where does the necessity come from, i.e. how could I have known?

Answer 1

The expression form you are using is called a Scalar Subquery. The manual says:

A scalar subquery is an ordinary SELECT query in parentheses that returns exactly one row with one column ... The SELECT query is executed and the single returned value is used in the surrounding value expression.

Your subquery returns a single value (which happens to be an array, prepared from the result of another subquery).

As a basic rule of thumb, subqueries are always in parenthesis.