Re: GSOC13 proposal - extend RETURNING syntax

On Thu, May 02, 2013 at 11:04:15AM +0200, Karol Trzcionka wrote:
> Hello,
> I'm student who want to participate in Google Summer of Code. I want to
> implement feature which allows to get old values directly from update
> statement. I mean there should be possibility to choose the value
> immedietly before or after update in RETURNING statement. The syntax may
> be realized as "aliases". That means: OLD keywordswould be alias to row
> before update and NEW to row after update. The conclusion of syntax is:
> UPDATE foo SET bar=bar+1 RETURNING OLD.bar AS old_bar, NEW.bar AS new_bar;
> UPDATE foo SET ... RETURNING NEW.* will be equivalent to UPDATE foo SET
> ... RETURNING foo.*
> It may be possible to add similar syntax to DELETE and INSERT statements
> but I'm not sure if it makes a sense (OLD for DELETE will be alias to
> row before delete, NEW for INSERT will be alias to row after insert and
> all triggers - however what about NEW for delete and OLD for INSERT?).
> Additionally NEW and OLD values will be reserved keywords (it might be
> some capability problem since in new PostgreSQL it isn't reserved -
> however standard says it is and in old PgSQL it was).
> I'd like to hear (read) yours feedback about syntax and/or implement
> issues related to this proposal.
> Regards,
> Karol Trzcionka

I would like to include the proposal as we've hammered it out together
on IRC and on GSoC site below.

Cheers,
David.

1. As the SQL standard mandates that OLD and NEW be reserved words, we'll re-reserve them.

2. Let's make OLD and NEW have the same meaning that INSERT/UPDATE/DELETE have when returning rows from the changed table. In particular

INSERT INTO foo (...) RETURNING NEW.*

will be equivalent to

INSERT INTO foo(...) RETURNING foo.*

Similarly for UPDATE and DELETE:

UPDATE foo SET ... RETURNING NEW.*

will be equivalent to

UPDATE foo SET ... RETURNING foo.*

and

DELETE FROM foo ... RETURNING OLD.*

will be equivalent to

DELETE FROM foo ... RETURNING foo.*

As RETURNING clauses have access to everything in the FROM/USING clause, it is important to limit the NEW/OLD rows as being only those in the table being written to in the statement.

3. Let's add an option to UPDATE so that it can RETURN OLD with the same characteristics as above, namely that it refers only to constants and columns in the updated table and not to everything available from the USING clause if included.

--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate