Re: [PATCHES] INSERT ... RETURNING
Bruce, Are you still working on completing this? Attached is my latest rewrite of this patch. Basically, it adds RETURNING to INSERT/UPDATE/DELETE, and allows arbitrary expressions. I'm still not sure how to handle DELETE ... USING and UPDATE ... FROM as I don't see a way to project the expressions against multiple relations with just an EState to work from. That said, Tom has suggested I redo this using resjunk -- so I'll do just that for my next revision. Regards, Omar Index: doc/src/sgml/keywords.sgml === RCS file: /home/omar/cvs/cvs/pgsql/doc/src/sgml/keywords.sgml,v retrieving revision 2.15 diff -C6 -r2.15 keywords.sgml *** doc/src/sgml/keywords.sgml 27 Nov 2004 21:27:06 - 2.15 --- doc/src/sgml/keywords.sgml 30 Jul 2005 06:37:02 - *** *** 3234,3245 --- 3234,3252 entry/entry entrynon-reserved/entry entrynon-reserved/entry entrynon-reserved/entry /row row + entrytokenRETURNING/token/entry + entryreserved/entry + entry/entry + entry/entry + entry/entry +/row +row entrytokenRETURNS/token/entry entrynon-reserved/entry entryreserved/entry entryreserved/entry entry/entry /row Index: doc/src/sgml/ref/delete.sgml === RCS file: /home/omar/cvs/cvs/pgsql/doc/src/sgml/ref/delete.sgml,v retrieving revision 1.24 diff -C6 -r1.24 delete.sgml *** doc/src/sgml/ref/delete.sgml8 Apr 2005 00:59:58 - 1.24 --- doc/src/sgml/ref/delete.sgml30 Jul 2005 06:37:02 - *** *** 20,31 --- 20,32 refsynopsisdiv synopsis DELETE FROM [ ONLY ] replaceable class=PARAMETERtable/replaceable [ USING replaceable class=PARAMETERusinglist/replaceable ] [ WHERE replaceable class=PARAMETERcondition/replaceable ] + [ RETURNING replaceable class=PARAMETERexpression/replaceable [, ...] ] /synopsis /refsynopsisdiv refsect1 titleDescription/title *** *** 102,131 A value expression that returns a value of type typeboolean/type that determines the rows which are to be deleted. /para /listitem /varlistentry /variablelist /refsect1 refsect1 titleOutputs/title para !On successful completion, a commandDELETE/ command returns a command !tag of the form screen DELETE replaceable class=parametercount/replaceable /screen The replaceable class=parametercount/replaceable is the number of rows deleted. If replaceable class=parametercount/replaceable is 0, no rows matched the replaceable class=parametercondition/replaceable (this is not considered an error). /para /refsect1 refsect1 titleNotes/title para --- 103,155 A value expression that returns a value of type typeboolean/type that determines the rows which are to be deleted. /para /listitem /varlistentry + +varlistentry + term + literalRETURNING/literal + replaceable class=PARAMETERexpression/replaceable [, ...] + /term + listitem + para +An optional list of expressions to return. + /para + /listitem +/varlistentry /variablelist /refsect1 refsect1 titleOutputs/title para !On successful completion, a commandDELETE/ command without a !literalRETURNING/literal clause returns a command tag of the form screen DELETE replaceable class=parametercount/replaceable /screen The replaceable class=parametercount/replaceable is the number of rows deleted. If replaceable class=parametercount/replaceable is 0, no rows matched the replaceable class=parametercondition/replaceable (this is not considered an error). /para + + indexterm zone=sql-delete +primaryRETURNING/primary +secondaryDELETE/secondary + /indexterm + + para +If a literalRETURNING/literal clause is present, the expression +specified is evaluated for each deleted row and the result is +returned. + /para /refsect1 refsect1 titleNotes/title para *** *** 158,183 programlisting DELETE FROM films WHERE kind lt;gt; 'Musical'; /programlisting /para para Clear the table literalfilms/literal: programlisting DELETE FROM films; /programlisting /para /refsect1 refsect1 titleCompatibility/title para This command conforms to the SQL standard, except that the !literalUSING/ clause and the ability to reference other tables !in the literalWHERE/ clause are productnamePostgreSQL/ extensions. /para /refsect1 /refentry !-- Keep this comment at the end of the file --- 182,220 programlisting DELETE FROM films WHERE kind lt;gt; 'Musical';
Re: [PATCHES] INSERT ... RETURNING
Are you still working on completing this? --- [EMAIL PROTECTED] wrote: Hi there, Attached is a patch (by Gavin Sherry, fixed up to apply to 8.1 by me) that implements INSERT ... RETURNING functionality. It does work for the common case of RETURNING the value of a serial/sequence column, but gets confused when returning results out-of-order (CREATE TABLE x (a int, b int), INSERT ... RETURNING b, a) and doesn't let you specify the same column multiple times (INSERT ... RETURNING b, b). These will be addressed soon. Regards, Omar Kilani [ Attachment, skipping... ] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] INSERT ... RETURNING
Here here on this one. With the deprecation of oids on the horizon insert returning is to be extremely important. It's use with the uniqueidentifier mod would be really really helpful. On a similar note, is anyone working on the ability to have a column default as the product of a function on another column of the same row. I know this can be done as a trigger but something like CREATE TABLE abc (name text not null unique, hash not null default somehashfunc(name)); would be very convenient, and of course with the ability to get the product back with insert returning. Kevin McArthur - Original Message - From: Bruce Momjian pgman@candle.pha.pa.us To: [EMAIL PROTECTED] Cc: pgsql-patches@postgresql.org Sent: Friday, July 29, 2005 7:26 PM Subject: Re: [PATCHES] INSERT ... RETURNING Are you still working on completing this? --- [EMAIL PROTECTED] wrote: Hi there, Attached is a patch (by Gavin Sherry, fixed up to apply to 8.1 by me) that implements INSERT ... RETURNING functionality. It does work for the common case of RETURNING the value of a serial/sequence column, but gets confused when returning results out-of-order (CREATE TABLE x (a int, b int), INSERT ... RETURNING b, a) and doesn't let you specify the same column multiple times (INSERT ... RETURNING b, b). These will be addressed soon. Regards, Omar Kilani [ Attachment, skipping... ] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] INSERT ... RETURNING
Tom Lane wrote: - should work for UPDATE and DELETE too And probably INSERT ... SELECT as well. -Neil ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] INSERT ... RETURNING
[EMAIL PROTECTED] writes: Attached is a patch (by Gavin Sherry, fixed up to apply to 8.1 by me) that implements INSERT ... RETURNING functionality. It does work for the common case of RETURNING the value of a serial/sequence column, but gets confused when returning results out-of-order (CREATE TABLE x (a int, b int), INSERT ... RETURNING b, a) and doesn't let you specify the same column multiple times (INSERT ... RETURNING b, b). These will be addressed soon. This is pretty considerably shy of what I thought had been agreed to anyway: - should allow expressions not only column names - should work for UPDATE and DELETE too regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
