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?

harmic · Accepted Answer · 2015-06-05 06:50:55Z

up vote 1 down vote accepted

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.

answered Jun 5 '15 at 6:50

harmic

13.8k22342

Ah, very interesting. I usually wrap subselects in parens, but I did not know where the requirement comes from. Scalar subquery explains it. Thank you! – mknecht Jun 5 '15 at 7:25

add a comment |

asked	2 years, 2 months ago
viewed	60 times
active	2 years, 2 months ago

Syntax error passing SQL result to PostgreSQL function accepting array

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged sql arrays postgresql postgresql-9.3 or ask your own question.

Hot Network Questions

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql arrays postgresql postgresql-9.3 or ask your own question.

Related