Обсуждение: DELETE RETURNING
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? It might be a good example to put into the docs then. If not, it may be worth the effort to make it work in 8.3. 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
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 > > It might be a good example to put into the docs then. > > If not, it may be worth the effort to make it work in 8.3. > > > 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 >
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