From: Tom Lane <tgl@sss.pgh.pa.us>
To: Paul McGarry <paulm@opentec.com.au>
To: pgsql-bugs@postgresql.org
To: pgsql-hackers@postgresql.org
Subject: Re: [SQL] MAX() of 0 records.
Date: Sun, 09 Jul 2000 14:35:40 -0400
Message-ID: <29285.963167740@sss.pgh.pa.us> (raw)
In-Reply-To: <9124.962953563@sss.pgh.pa.us>
References: <396535EF.8132CF80@opentec.com.au>
<8477.962940582@sss.pgh.pa.us>
<39656BD5.801BDEDC@opentec.com.au>
<9124.962953563@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, paulm@opentec.com.au, pgsql-bugs@postgresql.org, pgsql-hackers@postgresql.org
Subject: Re: [SQL] MAX() of 0 records.
In-Reply-To: <29285.963167740@sss.pgh.pa.us>
References: <396535EF.8132CF80@opentec.com.au> <8477.962940582@sss.pgh.pa.us> <39656BD5.801BDEDC@opentec.com.au> <9124.962953563@sss.pgh.pa.us> <29285.963167740@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox