Re: Using results from INSERT ... RETURNING

On Sat, Jul 18, 2009 at 5:21 PM, Jaime
Casanova<jcasanov(at)systemguards(dot)com(dot)ec> wrote:
> my questions first:
> - what's the use case for this?

Being able to use 'returning' in a subquery is probably the #1 most
requested feature for postgresql (it's also a todo). Solving it for
'with' queries is a nice step in the right direction, and sidesteps
some of the traps that result from the general case. There are many
obvious ways this feature is helpful...here's a couple:

move records from one table to another:
with foo as (delete from bar where something returning *) insert
insert into baz select foo.*:

gather defaulted values following an insert for later use:
with foo as (insert into bar(field) select 'hello' from
generate_series(1,n) returning *) insert into baz select foo.*;

merlin