Re: GSOC13 proposal - extend RETURNING syntax - Mailing list pgsql-hackers

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
beingonly 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
refersonly to constants and columns in the updated table and not to everything available from the USING clause if
included.

-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.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