PGSQL删除级联

Question

下面是我的表格：

create table location (
    nom text primary key,
    adresse text
);
create table groupe (nom text primary key);
create table groupelocation (
    nomGroupe text references groupe (nom) on delete cascade,
    nomLocation text references location (nom) on delete cascade,
    primary key(nomGroupe, nomLocation)
);
insert into groupe values('groupe');
insert into location values('location', 'là bas');
insert into groupelocation values('groupe', 'location');

我希望当我在组的表中删除一些东西时，删除所有关联的位置，但我找不到解决方案:/我已经尝试这样做：create rule deletelocations as on delete to groupe do instead (delete from (select location natural join groupelocation where old.nom = nomGroupe));但这不起作用:/你能帮我吗？

Answer 1

Postgres不支持向DELETE语句提供级联，但它支持在表创建时进行级联删除。

http://www.postgresql.org/docs/9.0/static/sql-createtable.html

级联分别删除引用已删除行的任何行，或将引用列的值更新为被引用列的新值。

Answer 2

有一个解决方案，我创建了一个小的plpgsql函数来解决这个问题

CREATE OR REPLACE FUNCTION deletelocation()
RETURNS text AS $$
DECLARE
    idloc int;
    curs refcursor;
BEGIN
    OPEN curs FOR SELECT location.id FROM location except SELECT location.id FROM location JOIN groupelocation ON location.id = groupelocation.idLocation;
    LOOP
        FETCH curs INTO idloc;
        EXIT WHEN NOT FOUND;
        DELETE FROM location WHERE id = idloc;
    END LOOP;
    CLOSE curs; 
    RETURN '';
END;
$$ language plpgsql;