PostgreSQL递归删除查询报UNION语法错误，求级联删除方案

我数据库内有多张存在外键关联的表，希望编写一条删除语句，依据特定条件删除所有关联表中的数据。但我编写的递归查询出现“UNION附近语法错误”，若有更优实现方案，欢迎分享。

我的SQL代码：

WITH RECURSIVE delete_query AS (
    DELETE FROM my_table WHERE id = 1 RETURNING id
    UNION
    SELECT t.table_name, c.column_name
    FROM information_schema.table_constraints t
    JOIN information_schema.constraint_column_usage c ON t.constraint_name = c.constraint_name
    WHERE t.constraint_type = 'FOREIGN KEY'
    AND c.table_name = delete_query.table_name
    AND c.column_name = delete_query.column_name
)
DELETE FROM delete_query
USING information_schema.columns c
WHERE delete_query.table_name = c.table_name
    AND delete_query.column_name = c.column_name
    AND c.table_schema = 'public';

你的递归CTE写法存在几个核心问题：

• UNION两侧结果集不兼容：第一个分支仅返回id（单数值列），第二个分支返回table_name和column_name（双字符串列），列数、类型均不匹配，直接触发语法错误。
• CTE无法作为删除目标：delete_query是递归生成的元数据（表名、列名），并非实际数据存储表，不能直接执行DELETE FROM delete_query。
• 递归逻辑方向错误：需从目标主表出发，反向查找所有依赖它的子表（被外键关联的表），而非正向关联。

可行实现方案 #

方案1：手动按依赖顺序删除（适合简单表结构）

先删除所有依赖主表的子表数据，再删除主表数据，示例：

-- 先删除依赖my_table的子表1数据
DELETE FROM child_table1 WHERE my_table_id = 1;
-- 再删除依赖子表1的子表2数据
DELETE FROM child_table2 WHERE related_id IN (SELECT id FROM child_table1 WHERE my_table_id = 1);
-- 最后删除主表数据
DELETE FROM my_table WHERE id = 1;

优点：简单直观，易调试；缺点：表数量多的时候需手动梳理依赖关系。

方案2：PL/pgSQL递归生成动态删除语句（适合复杂表结构）

编写存储过程，自动递归查找所有依赖主表的子表，生成并执行动态SQL：

CREATE OR REPLACE FUNCTION delete_cascade_custom(p_table_name text, p_id_value int)
RETURNS void AS $$
DECLARE
    rec record;
BEGIN
    -- 递归遍历所有依赖当前表的子表
    FOR rec IN
        SELECT DISTINCT tc.table_name, kcu.column_name
        FROM information_schema.table_constraints tc
        JOIN information_schema.key_column_usage kcu
            ON tc.constraint_name = kcu.constraint_name
        JOIN information_schema.constraint_column_usage ccu
            ON tc.constraint_name = ccu.constraint_name
        WHERE tc.constraint_type = 'FOREIGN KEY'
          AND ccu.table_name = p_table_name
          AND tc.table_schema = 'public'
    LOOP
        -- 先递归处理子表的关联数据
        PERFORM delete_cascade_custom(rec.table_name, p_id_value);
        -- 执行子表的删除操作
        EXECUTE format('DELETE FROM %I WHERE %I = $1', rec.table_name, rec.column_name)
        USING p_id_value;
    END LOOP;
    -- 最后删除主表目标数据
    EXECUTE format('DELETE FROM %I WHERE id = $1', p_table_name)
    USING p_id_value;
END;
$$ LANGUAGE plpgsql;

使用方式：

-- 删除my_table中id=1的所有关联数据
SELECT delete_cascade_custom('my_table', 1);

优点：自动处理所有外键依赖，无需手动梳理；缺点：需要创建存储过程的权限，执行前需验证逻辑避免误删。

方案3：利用PostgreSQL原生级联删除（最优推荐）

若允许修改表结构，给外键约束添加ON DELETE CASCADE，删除主表数据时数据库会自动清理关联子表数据：

-- 修改子表外键约束，添加级联删除规则
ALTER TABLE child_table1
DROP CONSTRAINT child_table1_my_table_id_fkey,
ADD CONSTRAINT child_table1_my_table_id_fkey
FOREIGN KEY (my_table_id) REFERENCES my_table(id) ON DELETE CASCADE;

之后直接执行主表删除即可：

DELETE FROM my_table WHERE id = 1;

优点：数据库原生支持，性能最优，无需额外代码；缺点：需修改表结构，级联规则永久生效，操作前需确认数据安全。

内容的提问来源于stack exchange，提问作者Vinay chaurasiya