Re: [HACKERS] DELETE RETURNING
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 signature.asc Description: OpenPGP digital signature
Re: [HACKERS] 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 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] 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 signature.asc Description: OpenPGP digital signature