Re: Updated INSERT/UPDATE RETURNING

Here's the updated patch with DELETE RETURNING removed. This isn't
really an issue because no one wanted DELETE RETURNING to begin with.

It is important to note that this patch is not yet ready to be
committed. I still need to go through and run some more tests on it
but wanted to put it in the queue again and let ya know I've been
given time to make sure it gets in.

This patch includes:
- Code changes to core
- Code changes to PL/pgSQL
- Preliminary Documentation Updates (need to add to PL/pgSQL docs)
- Preliminary Regression Tests (need to add PL/pgSQL regressions)

There were a couple suggestions for sorta-kewl features like being
able to use INSERT/UPDATE RETURNING in a FOR loop, etc. I may be able
to get those in if people really want it but I looked into it after
Neil mentioned it and IIRC, there are quite a few changes required to
support it.

Suggestions requested.

--
Jonah H. Harris, Software Architect | phone: 732.331.1300
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 2nd Floor | jharris(at)enterprisedb(dot)com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/

This is a great patch. I was hoping to get this into 8.2 as a major
feature.

---------------------------------------------------------------------------

Jonah H. Harris wrote:
> Here's the updated patch with DELETE RETURNING removed. This isn't
> really an issue because no one wanted DELETE RETURNING to begin with.
>
> It is important to note that this patch is not yet ready to be
> committed. I still need to go through and run some more tests on it
> but wanted to put it in the queue again and let ya know I've been
> given time to make sure it gets in.
>
> This patch includes:
> - Code changes to core
> - Code changes to PL/pgSQL
> - Preliminary Documentation Updates (need to add to PL/pgSQL docs)
> - Preliminary Regression Tests (need to add PL/pgSQL regressions)
>
> There were a couple suggestions for sorta-kewl features like being
> able to use INSERT/UPDATE RETURNING in a FOR loop, etc. I may be able
> to get those in if people really want it but I looked into it after
> Neil mentioned it and IIRC, there are quite a few changes required to
> support it.
>
> Suggestions requested.
>
> --
> Jonah H. Harris, Software Architect | phone: 732.331.1300
> EnterpriseDB Corporation | fax: 732.331.1301
> 33 Wood Ave S, 2nd Floor | jharris(at)enterprisedb(dot)com
> Iselin, New Jersey 08830 | http://www.enterprisedb.com/

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

"Jonah H. Harris" <jonah(dot)harris(at)gmail(dot)com> writes:
> Here's the updated patch with DELETE RETURNING removed. This isn't
> really an issue because no one wanted DELETE RETURNING to begin with.

Huh? Why'd you remove it? I can't imagine it makes things
significantly simpler to omit that case, and even if you can't
think of uses for it, I can (taking jobs from a to-do queue for
instance).

BTW, it occurs to me to wonder whether we've picked a good choice
of syntax. I don't remember where the suggestion to use "RETURNING"
came from (did we borrow it from another DBMS?). But AFAICS this
syntax will require the introducing keyword to be a fully reserved
word, and since RETURNING is not listed as a reserved word in the
SQL spec, reserving it is arguably a spec violation.

The simplest alternative that comes to mind is to use RETURNS instead of
RETURNING; since RETURNS is listed as reserved, this doesn't violate
spec. OTOH we currently treat RETURNS as an unreserved keyword, and
we'd have to promote it to fully reserved. It could be argued that
"returns" is a more likely name for a table column than "returning";
if so we'd actually be more likely to break existing apps this way.

I don't have a strong feeling either way, but now is the time to
decide.

> It is important to note that this patch is not yet ready to be
> committed.

OK, but we need a final version soon.

regards, tom lane

On 8/5/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Huh? Why'd you remove it? I can't imagine it makes things
> significantly simpler to omit that case, and even if you can't
> think of uses for it, I can (taking jobs from a to-do queue for
> instance).

It can be added back. Dequeing is a good use-case idea though :)

> BTW, it occurs to me to wonder whether we've picked a good choice
> of syntax. I don't remember where the suggestion to use "RETURNING"
> came from (did we borrow it from another DBMS?).

Oracle. DB2 uses something similar to SELECT * FROM (UPDATE tbl SET ... );

> But AFAICS this syntax will require the introducing keyword to be a fully reserved
> word, and since RETURNING is not listed as a reserved word in the
> SQL spec, reserving it is arguably a spec violation.

True.

> The simplest alternative that comes to mind is to use RETURNS instead
> I don't have a strong feeling either way, but now is the time to
> decide.

I don't care either way, RETURNS is fine I guess.

> OK, but we need a final version soon.

Sure thing.

--
Jonah H. Harris, Software Architect | phone: 732.331.1300
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 2nd Floor | jharris(at)enterprisedb(dot)com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/

"Jonah H. Harris" <jonah(dot)harris(at)gmail(dot)com> writes:
> On 8/5/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> BTW, it occurs to me to wonder whether we've picked a good choice
>> of syntax. I don't remember where the suggestion to use "RETURNING"
>> came from (did we borrow it from another DBMS?).

> Oracle. DB2 uses something similar to SELECT * FROM (UPDATE tbl SET ... );

Oh, okay.

>> The simplest alternative that comes to mind is to use RETURNS instead
>> I don't have a strong feeling either way, but now is the time to
>> decide.

> I don't care either way, RETURNS is fine I guess.

Well, if the syntax is compatible with Oracle as-is, that's probably
a sufficient reason to stick with it. It's not like we haven't got
any other non-spec reserved words ...

regards, tom lane

On 8/5/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Jonah H. Harris" <jonah(dot)harris(at)gmail(dot)com> writes:
> > Here's the updated patch with DELETE RETURNING removed. This isn't
> > really an issue because no one wanted DELETE RETURNING to begin with.

I don't have the time to add DELETE RETURNING back in. My initial
patch for this included it, so anyone can feel free to pick it up and
add it back. If no one wants to do this, I'd just say to add INS/UPD
version to 8.2 and I'll work on the DELETE version for 8.3.

As far as the current patch, I'm actually fine with someone reviewing
it as-is; I just wanted another look over it to see if I missed
something... but one of you may see that much quicker than I (as I'm
too close too it).

--
Jonah H. Harris, Software Architect | phone: 732.331.1300
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 2nd Floor | jharris(at)enterprisedb(dot)com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/