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 only takes a minute to sign up.
Sign up to join this community
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?
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.
Very simple indeed:
DELETE FROM batch bp
USING sender_log sl
WHERE bp.log_id = sl.id
RETURNING sl.packet
;-)
