Consider the following hypothetical SQL:
>
~~~
PRAGMA foreign_keys=on;
CREATE TABLE t1(
a INTEGER PRIMARY KEY,
b INTEGER REFERENCES t1 ON DELETE CASCADE
);
INSERT INTO t1(a,b) VALUES(0,null),(1,0),(2,1),(3,2),(4,null);
DELETE FROM t1 WHERE a=0 RETURNING a;
~~~
The final DELETE should delete rows 0, 1, 2, and 3 due to the
ON DELETE CASCADE clause, and indeed it does.
## Question:
Should the RETURNING clause return all four deleted rows, or
should it only return the specified row 0?
## Reasons to return all four rows:
1. If you have a RETURNING clause, presumably you want to see every
row that has been deleted, regardless of whether or not that row
was deleted directly or via a cascading delete.
2. If you don't return all rows, then the result of RETURNING can
vary according to the order of the delete. If the final statement
is "DELETE FROM t1 WHERE a IN (0,1,2,3) RETURNING a;" then you
might get "0 1 2 3" or you might get "0" or you might get something
in between depending on the delete order and the timing of the
cascading delete.
3. Triggers and other side effects fire on cascading deletes.
Why should RETURNING be any different?
## Reasons to return just the one row 0:
1. The RETURNING clause cannot report on cascading deletes in
other tables, since the argument to RETURNING must refer to columns
in the primary table. So if cascading deletes cannot reported in
all tables, why report them in the primary table? Shouldn't all
tables be treated the same?
2. PostgreSQL 9.6 works this way.