Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two queries:

The first one is as below:

SELECT * FROM batch bp INNER JOIN sender_log sl ON sl.id=bp.log_id AND bp.protocol='someprotocol'

The second query is used to delete rows:

delete from batch where protocol='someprotocol'

I know that in postgres we have RETURNING * to return deleted rows. What I want to achieve is to delete rows and return them in one query. How can I accomplish this?

share|improve this question

2 Answers 2

up vote 4 down vote accepted

Very simple indeed, but you do need to include the other WHERE clause as well:

DELETE FROM batch bp
USING  sender_log sl
WHERE  bp.log_id = sl.id
AND    bp.protocol = 'someprotocol'
RETURNING bp.*, sl.*;

And to actually return what your question outlines, you need to include both tables in the RETURNING clause.

share|improve this answer

Very simple indeed:

DELETE FROM batch bp
USING sender_log sl
WHERE bp.log_id = sl.id
RETURNING sl.packet

;-)

share|improve this answer
    
What does RETUNRNING sl.packet do? –  Mihai Apr 26 at 13:39
1  
@Mihai: I added a link to my answer, the manual explains. –  Erwin Brandstetter Apr 26 at 17:33
    
@ErwinBrandstetter, your answer fulfills what I've asked. thanks. +1 –  john.locke Apr 27 at 6:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.