Thread: 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