6

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?

13

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.

3

Very simple indeed:

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

;-)

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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