Re: Putting an aggregate value in an UPDATE statement...

Leif Biberg Kristensen <leif(at)solumslekt(dot)org> writes:
>> You need them to syntactically separate the sub-select from the outer
>> select. If SQL didn't require them, then in something like
>>
>> UPDATE question_choices SET total_rows =
>> select count(*) from care_lesson where something
>>
>> it wouldn't be clear whether the WHERE clause was meant to attach
>> to the sub-select or the outer UPDATE.

> A couple of days ago, a was a little stumped by this. I had written a plain
> SQL function with one integer parameter, and then tried to use a SELECT as
> input parameter as in

> SELECT myfunc(SELECT foo FROM bar WHERE baz);

> It took a while before I realized that I needed to put the query in another
> set of parentheses:

> SELECT myfunc((SELECT foo FROM bar WHERE baz));

> worked just fine. I fail to see the ambiguity here, though.

Well, the main point is that the possible ambiguity means that SQL has
mandated you put parentheses around any sub-select. However, if you're
claiming there is no possible ambiguity inside a function call, compare:

select myfunc((select integer_col from foo where bar = 5+4))
select myfunc((select integer_col from foo where bar = 5)+4)

These mean different things, and you couldn't tell 'em apart without
the inner parentheses.

regards, tom lane