How can one get hold of the deleted child record when the parent record is deleted in postgresql? I have two tables that are related. The foreign table have a foreign key that has the constraint of on delete cascade. I would like to get hold of the child record after deleting the parent record.
The tables are as follows:
create table table1(
id int not null primary key
);
create table table2(
id int not null primary key,
table1id int not null,
foreign key(table1id) references table1(id) on delete cascade
);
Assume the tables have records.
table1
| id |
+----+
| 1 |
+----+
table2
| id | table1id |
+----+----------+
| 1 | 1 |
+----+----------+
So I want to delete the records after retrieving the child records using one statement.
I tried DELETE FROM table1 WHERE EXISTS ( SELECT * FROM table2 WHERE id = 1) RETURNING * but it only returns the table1 records and not at least table2 record. table2 deleted record is what I want to use after the deletion. How can this be achieved?