I wrote:
> Comments anyone? What exactly *should* be the behavior of an UPDATE
> that uses an aggregate function and a join to another table? Over what
> set of tuples should the aggregate be evaluated?
Further note on this: SQL99 specifies:
<update statement: searched> ::= UPDATE <target table> SET <set clause list>
[ WHERE <search condition> ]
...
5) A <value expression> in a <set clause> shall not directly contain a <set function specification>.
so the construct is definitely not SQL-compliant. Maybe we should just
forbid it. However, if you are joining against another table (which
itself is not an SQL feature) then it seems like there is some potential
use in it. What do people think of my implicit-GROUP-BY-ctid idea?
That would basically say that the aggregate is computed over all the
tuples that join to a single target tuple.
regards, tom lane
