Skip site navigation (1) Skip section navigation (2)

insert ... delete ... returning ... ?

From: Mark Mielke <mark(at)mark(dot)mielke(dot)cc>
To: Postgresql-Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: insert ... delete ... returning ... ?
Date: 2008-02-24 21:21:27
Message-ID: 47C1DFD7.7060603@mark.mielke.cc (view raw or flat)
Thread:
Lists: pgsql-hackers
Hi all:

I'm at PostgreSQL 8.3 for my production database and everything is 
working great. I had no problems converting free text search from 8.2 to 
8.3, and I really like the improvements.

I tried using insert ... delete ... return ... and get a syntax error:

> pccyber=# insert into product_manufacturer_archived (itemno, 
> manufacturer_id)
> pccyber-#   delete from product_manufacturer
> pccyber-#     where not exists (select * from icitem
> pccyber(#                         where icitem.itemno = 
> product_manufacturer.itemno and
> pccyber(#                               not inactive)
> pccyber-#     returning itemno, manufacturer_id;
> ERROR:  syntax error at or near "delete"
> LINE 2:   delete from product_manufacturer
>           ^

The goal here is to move inactive records to an archived table. This is 
to be performed as part of a daily batch job instead of as a trigger. 
Assume my model is correct - my question isn't how can I do this. I 
would like to know if insert .. delete .. returning is intended to work 
or not.

In the past I've executed insert ... select and then the delete. 
However, I believe there is race condition here as the delete may see 
more or less rows than the insert ... select. I thought the above would 
be a clever PostgreSQL-8.3 alternative, but I'm either stupid or it 
doesn't work... :-)

Any ideas?

Thanks,
mark

-- 
Mark Mielke <mark(at)mielke(dot)cc>


Responses

pgsql-hackers by date

Next:From: Guillaume SmetDate: 2008-02-24 21:39:03
Subject: Re: insert ... delete ... returning ... ?
Previous:From: Florian G. PflugDate: 2008-02-24 19:25:34
Subject: Re: Behaviour of rows containg not-null domains in plpgsql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group