DELETE RETURNING

On 25-Sep-06, at 4:31 AM, Markus Schaber wrote:

Hi,

I just read the docs about DELETE RETURNING in 8.2, and a small
idea arised:

INSERT INTO logtable DELETE * FROM lifetable WHERE date<'2006-01-01'
RETURNING *;

Will this work as expected?

What is your expected result here ? It would return all the rows that
were deleted ?

Dave

Hi, Dave,

Dave Cramer wrote:

I just read the docs about DELETE RETURNING in 8.2, and a small idea
arised:

INSERT INTO logtable DELETE * FROM lifetable WHERE date<'2006-01-01'
RETURNING *;

Will this work as expected?

What is your expected result here ? It would return all the rows that
were deleted ?

Yes. It would be shorthand for "moving" rows between tables, faster than
first using "INSERT INTO .. SELECT" and then "DELETE" afterwards, as it
saves at least on table scan.

The question is whether INSERT INTO only allows SELECT as data source,
or every query returning a ResultSet.

I don't see any usecases for using UPDATE RETURNING and INSERT RETURNING
as data source for INSERT INTO yet, especially as UPDATE RETURNING
returns the new versions of the rows.

And I see that the same behaviour could be achieved with triggers, but
with much higher overhead for non-regular tasks.

Thanks,
Markus

--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org