postgresql - postgresql中删除其他表引用的记录的过程

Question

我正在尝试在 Postgresql 中创建一个存储过程，它允许我在创建表时不使用 DELETE CASCADE 从其他表中删除外键，但给我一个错误，即在其他表中仍然引用了一个 id。这是我尝试过的代码：

CREATE OR REPLACE PROCEDURE sp_delete_restaurante_msg
                           ( _id_restaurante   integer, 
                             _id_local         integer, 
                             _nome_restaurante varchar(100), 
                             INOUT _msg        varchar(100), 
                             INOUT _msg1       varchar(100) )
LANGUAGE plpgsql
AS $$
DECLARE

    loc_cursor CURSOR FOR SELECT local.id_local FROM local WHERE local.id_local = _id_local FOR UPDATE;
    ementa_cursor CURSOR FOR SELECT ementa.id_restaurante FROM ementa WHERE ementa.id_restaurante = _id_restaurante FOR UPDATE;
    func_cursor CURSOR FOR SELECT funcionario.id_restaurante FROM funcionario WHERE funcionario.id_restaurante = _id_restaurante FOR UPDATE;
    apont_loc RECORD;
    apont_em RECORD;
    apont_func RECORD;

BEGIN

    IF EXISTS(SELECT l.id_local, rest.id_restaurante FROM local l JOIN restaurante rest ON l.id_local = rest.id_local WHERE rest.id_restaurante = _id_restaurante AND
              l.id_local = _id_local) THEN
        DELETE FROM restaurante WHERE restaurante.id_restaurante = _id_restaurante;
        COMMIT;
    ELSE
        IF (SELECT COUNT(*) FROM local WHERE local.id_local = _id_local) > 0 THEN
        OPEN loc_cursor;
        LOOP
            FETCH NEXT FROM loc_cursor INTO apont_loc;
            EXIT WHEN NOT FOUND;
            IF apont_loc.id_local = _id_local THEN
                DELETE FROM local WHERE CURRENT OF loc_cursor;
                COMMIT;
            END IF;
        END LOOP;
        CLOSE loc_cursor;
        COMMIT;
        ELSIF (SELECT COUNT(*) FROM ementa WHERE ementa.id_restaurante = _id_restaurante) > 0 THEN
            OPEN ementa_cursor;
            LOOP
                FETCH NEXT FROM ementa_cursor INTO apont_em;
                EXIT WHEN NOT FOUND;
                IF apont_em.id_restaurante = _id_restaurante THEN
                    DELETE FROM ementa WHERE CURRENT OF ementa_cursor;
                    COMMIT;
                END IF;
            END LOOP;
            CLOSE ementa_cursor;
            COMMIT;
        ELSIF (SELECT COUNT(*) FROM funcionario WHERE funcionario.id_restaurante = _id_restaurante) > 0 THEN
            OPEN func_cursor;
            LOOP
                FETCH NEXT FROM func_cursor INTO apont_func;
                EXIT WHEN NOT FOUND;
                IF apont_func.id_restaurante = _id_restaurante THEN
                    DELETE FROM funcionario WHERE CURRENT OF func_cursor;
                    COMMIT;
                END IF;
                END LOOP;
                CLOSE func_cursor;
        ELSE
            RAISE NOTICE 'Nao existe mais tabelas ligadas';
        END IF;
    END IF;
    IF(SELECT COUNT(*) FROM restaurante WHERE restaurante.id_restaurante = _id_restaurante) = 0 THEN
        RAISE EXCEPTION '%', _msg;
        ROLLBACK;
    END IF;
    IF _nome_restaurante IS NULL OR _nome_restaurante = '' THEN
        RAISE EXCEPTION '%', _msg1;
        ROLLBACK;
    END IF;
END;
$$

标签： postgresqlcursorplpgsqlsql-delete