Re: WITH x AS (...) and visibility in UPDATE

On 7/27/2011 4:22 PM, Peter V wrote:
> I want to apply updates on a copy of a row, instead on the row itself.
> The queries are above were simplied to demonstrate the 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.
>
Maybe I'm totally missing something, but why insert a copy and then
update instead of directly insert a mutated copy?

Something like:
INSERT INTO t (foo, bar) SELECT 'my new foo', t.bar FROM t WHERE id=123;

Wouldn't the above construction let you make a new row with some new
values and some copied values?

--
Jack Christensen
jackc@hylesanderson.edu