SQL 'DELETE CASCADE'

I have a table B that has a foreign key to table A, and now I want to do a sorta "DELETE CASCADE" thingie on A, but PostgreSQL won't accept the following:

DELETE FROM ATable WHERE aid IN
(
    DELETE FROM BTable WHERE aid IN
    (
        ... [expression that depends on the entries in BTAble] ...
    )
    RETURNING aid
);

Seems that only SELECT can be inside the IN () clause. I su开发者_如何学Pythonppose there is some easy (and standard SQL, not PostgreSQL-specific?) way of doing this?

Edit: is it safe to say that something is badly structured when you bump into this kind of problem? In our case I have a gut feeling that the hits in ..[expr].. should be in a new CTAble instead of as a subset in ATable, but I can't really point to any design paradigm to support that.

This will be possible with PostgreSQL 9.1, but I don't think there is any way doing that without defining cascading constraints.

http://developer.postgresql.org/pgdocs/postgres/queries-with.html#QUERIES-WITH-MODIFYING

You can wait for 9.1 or create a set-returning function meanwhile:

CREATE OR REPLACE FUNCTION fn_delete_btable(params)
RETURNS SETOF btable.aid%TYPE
AS
$$
        DELETE
        FROM    btable
        WHERE   expression_over_params(params)
        RETURNING 
                aid
$$
LANGUAGE 'sql';

DELETE
FROM    atable
WHERE   aid IN
        (
        SELECT  aid
        FROM    fn_delete_btable(params)
        )

P.S. Just in case if you're not aware about standard SQL way of doing it.

If you create the tables like this:

CREATE TABLE btable (aid INT NOT NULL UNIQUE, …)
CREATE TABLE atable (aid INT NOT NULL FOREIGN KEY REFERENCES (aid) ON DELETE CASCADE, …)

then a delete from btable will trigger a delete from atable as well.

For this to work, btable.aid should be UNIQUE or a PRIMARY KEY, and this is less efficient for mass updates than a set-based solution.

You should be able to do that: here is an example that I found on the bottom of this page.

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

DB functions are outside my comfort zone (I know, I know) and I didn't want to make even temporary changes to the columns in question so I simply did a

CREATE TABLE CTable AS ... [expression that depends on BTAble] ...;

and used that to sequentially delete data in B and A.