Re: Writeable CTEs and side effects

On Thu, Oct 08, 2009 at 11:54:08AM -0400, Merlin Moncure wrote:
> On Thu, Oct 8, 2009 at 6:52 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> > up to the main query.  For what may turn out to be pretty common uses
> > cases like WITH tuples AS (DELETE FROM big_table_1 RETURNING ...)
> > INSERT INTO big_table_2 ... this is going to suck pretty bad.  I
>
> Is the above form:
>
> with x as (delete .. returning *) insert into y select * from x
>
> going to be allowed? I was informed on irc that it wasn't...it would
> have to be written as:
>
> insert into y with x as (delete .. returning *) select * from x
>
> IOW, will WITH be able to terminate in update/insert/delete and not just select?

Having INSERT/UPDATE/DELETE after the CTE definition was part of my
original idea for the feature. To keep changes as small as possible,
would it be OK to have the more verbose equivalent to start?

Or have you come up with a case where the more verbose one just
doesn't work?

Cheers,
David (not going into the RECURSIVE version just yet ;)
--
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

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