Re: Writeable CTEs

On Tue, Jan 05, 2010 at 05:21:12PM +0000, Greg Stark wrote:
> On Tue, Jan 5, 2010 at 4:42 PM, Marko Tiikkaja
> <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> wrote:
> > => with t as (delete from foo returning *)
> > -> insert into bar
> > -> select * from t;
> > INSERT 0 2
> >
> > It correctly reports 2 affected rows (one deleted and one
> > inserted), but is this the answer we want?  It doesn't seem all
> > that useful to know the total amount of affected rows.
>
> My first thought is that the number should correspond to the INSERT.
> It didn't INSERT two rows so it seems wrong. More importantly in a
> case like
>
> with t as (delete from foo returning *) select * from t where x=?
>
> applications will almost certainly expect the number to match the
> actual number of rows returned and may well misbehave if they don't.

I'm not sure how relevant this could be, as existing apps can't use
future functionality. We have precedents with RULEs, which can make
the arguments pretty meaningless.

In some future version, we may want to redo the infrastructure to
support "modified" values for multiple statements, but for now, that
seems like an unnecessary frill.

Cheers,
David.
--
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