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

Leif Biberg Kristensen <leif@solumslekt.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