----------------------------------------
> Date: Wed, 27 Jul 2011 16:16:48 -0500
> Subject: Re: [GENERAL] WITH x AS (...) and visibility in UPDATE
> From: mmoncure@gmail.com
> To: peterv861908@hotmail.com
> CC: pgsql-general@postgresql.org
>
> On Wed, Jul 27, 2011 at 4:03 PM, Peter V <peterv861908@hotmail.com> wrote:
> >> On Wed, Jul 27, 2011 at 3:18 PM, Peter V <peterv861908@hotmail.com> wrote:
> >> >
> >> > Hello all,
> >> >
> >> > I am trying out PostgreSQL 9.1 Beta 3. In particular, I am very interested in WITH x AS (...) construction.
> >> >
> >> > drop table if exists t;
> >> > create table t
> >> > (
> >> > identifier serial,
> >> > title text
> >> > );
> >> >
> >> > with c as
> >> > (
> >> > insert into t (title) values ('old') returning *
> >> > )
> >> > update t set title = 'new' from c where t.identifier = c.identifier;
> >> >
> >> > select * from t;
> >> >
> >>
> > > Can someone explain why this returns 'old' instead of 'new'? Is
> > the new row not yet visible when the update is evaluated?
> >>
> >> because the update statement isn't doing anything. (you could have
> >> confirmed this by adding 'returning *' to the update.
> >>
> >> While the 'from c' is working, you can't join back to t yet because
> >> the statement hasn't resolved. here's a reduced form of your problem:
> >>
> >> postgres=# with c as
> >> (
> >> insert into t (title) values ('old') returning *
> >> ) select * from t join c using (identifier);
> >>
> >> The join fails because at the time it happens t isn't yet populated.
> >>
> >> merlin
> >
> >
> >
> > This makes sense. I thought that the insert was evaluated first, before the join is resolved. This isn't the case
apparently.
> >
> > Is there another way to force this? That is, without extracting it to two statements ;)
>
> not in the exact sense you were trying. what is it you are trying to
> do in general? I'm having trouble understanding your use-case.
>
I want to apply updates on a copy of a row, instead on the row itself. The queries are above were simplied to
demonstratethe problem.
So basically I want to do:
1) create the copy of the row and return the identifier
2) apply updates on the new row identified by the identifier returned in step 1
If possible, I want to write this in a single command, to avoid overhead and mistakes.
I tried writing a rewrite rule or before trigger, but it becomes quickly a mess to avoid infinite loops.
Any ideas are welcome. Thanks.
