Обсуждение: insert into t1 (delete from t0 returning *)

in 8.3beta3

create table t0(c1 int);
create table t1(c1 int);

insert into t0 values (1);
insert into t0 values (2);
insert into t0 values (3);

If I execute "delete from t0 returning *" it deletes the rows and returns the deleted rows.  I could insert all those rows into t1 by doing "insert into t1 (select * from t0)".  I would expect to be able to do the same with "returning *".  In particular, I would expect "insert into t1 (delete from t0 returning *)" to delete the rows from t0 and insert them into t1 but I get a syntax error - is that expected?

On Dec 17, 2007 6:56 PM, Matthew Dennis <mdennis@merfer.net> wrote:
> in 8.3beta3
>
> create table t0(c1 int);
> create table t1(c1 int);
>
> insert into t0 values (1);
>  insert into t0 values (2);
>  insert into t0 values (3);
>
> If I execute "delete from t0 returning *" it deletes the rows and returns
> the deleted rows.  I could insert all those rows into t1 by doing "insert
> into t1 (select * from t0)".  I would expect to be able to do the same with
> "returning *".  In particular, I would expect "insert into t1 (delete from
> t0 returning *)" to delete the rows from t0 and insert them into t1 but I
> get a syntax error - is that expected?

Yes.

This is a 'faq' (although I don't think it's on the faq, maybe it should be.
It is harder than it looks.
Everyone wants it.

merlin