mysql - 删除相关数据表的Postgres程序

Question

Halo，我是 postgresql 的新手，现在我有一个将数据库从 mysql 迁移到 postgresql 的项目。我想在删除父数据时触发，子数据也将被删除。这是结构描述:

• table_A(触发器)

  • 表_B
    • 表_C
    • 表_D

这是我试过的代码:

CREATE OR REPLACE FUNCTION delete_relation() RETURNS trigger AS
$$
BEGIN
    DELETE FROM table_C 
        USING table_B 
        WHERE table_C.id = table_B.id_C;
    DELETE FROM table_C 
        USING table_B 
        WHERE table_D.id = table_B.id_D;
    DELETE FROM table_B WHERE table_B.id_A = OLD.id;    
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER 
    delete_table_A
AFTER DELETE ON 
    table_A 
FOR EACH ROW EXECUTE PROCEDURE delete_relation();

然后，当我执行时，它将返回:

Function executed no return statement: 7 
ERROR: control reached end of trigger procedure without RETURN
CONTEXT: PL/pgSQL function delete_relation()

Answer 1

如果不判断您完成特定任务的方法的效率，错误消息非常明显，意味着您缺少 the documentation 要求的 RETURN 语句。 :

A trigger function must return either NULL or a record/row value having exactly the structure of the table the trigger was fired for.

对于 AFTER 触发器，这应该是:

...

RETURN NULL;
...