PostgreSQL中IF/THEN语句使用DELETE FROM...RETURNING报错如何解决

你的语法报错来自两个核心逻辑错误：

• RETURNS TABLE 是PostgreSQL创建自定义函数/存储过程时用来定义返回值结构的语法，而你写的是DO开头的匿名PL/pgSQL代码块，匿名块本身不支持定义返回值，也无法直接将RETURNING的结果输出到客户端，所以写在DO块头部会直接触发语法错误。
• 即使删掉RETURNS TABLE行，匿名块内的DELETE...RETURNING的结果也只会被丢弃，不会返回给查询端。

方案1：创建自定义函数（推荐，适合逻辑复用场景） #

这是最通用稳定的写法，函数定义后可以随时调用获取结果：

-- 创建返回指定结构的函数
CREATE OR REPLACE FUNCTION test_data_operate(
    p_id INT DEFAULT 100,
    p_new_id INT DEFAULT NULL
) RETURNS TABLE (msg VARCHAR(500), isSuccessful BIT)
LANGUAGE plpgsql
AS $$
BEGIN
    IF p_new_id IS NULL THEN
        -- 先删除table1对应行
        DELETE FROM table1 t1 WHERE t1.id = p_id;
        -- 删除table2同时返回指定结果
        RETURN QUERY
            DELETE FROM table2 t2 WHERE t2.id = p_id
            RETURNING 'test'::VARCHAR(500), '1'::BIT;
    ELSE
        -- 插入场景逻辑
        INSERT INTO table1(id, name) VALUES(p_id, 'testname');
        -- 插入场景也需要返回结果的话保留下面一行，不需要可以删除
        RETURN QUERY SELECT 'insert success'::VARCHAR(500), '1'::BIT;
    END IF;
END;
$$;

-- 调用函数直接获取返回结果
SELECT * FROM test_data_operate();

方案2：临时函数（不需要永久留存函数定义） #

如果不想在数据库中留下永久函数，可以创建会话级临时函数，会话关闭后自动删除：

-- 创建临时函数，存在pg_temp schema下
CREATE OR REPLACE FUNCTION pg_temp.test_data_operate(
    p_id INT DEFAULT 100,
    p_new_id INT DEFAULT NULL
) RETURNS TABLE (msg VARCHAR(500), isSuccessful BIT)
LANGUAGE plpgsql
AS $$
BEGIN
    IF p_new_id IS NULL THEN
        DELETE FROM table1 t1 WHERE t1.id = p_id;
        RETURN QUERY
            DELETE FROM table2 t2 WHERE t2.id = p_id
            RETURNING 'test'::VARCHAR(500), '1'::BIT;
    ELSE
        INSERT INTO table1(id, name) VALUES(p_id, 'testname');
        RETURN QUERY SELECT 'insert success'::VARCHAR(500), '1'::BIT;
    END IF;
END;
$$;

-- 调用临时函数
SELECT * FROM pg_temp.test_data_operate();

方案3：可写CTE单SQL实现（无需创建函数） #

如果不想用函数，也可以用PostgreSQL的可写CTE特性，单条SQL完成逻辑并返回结果，不需要写PL/pgSQL块：

WITH params AS (
    -- 在这里定义参数，修改即可适配不同输入
    SELECT
        100 AS id,
        NULL::INT AS new_id
),
del_t1 AS (
    -- new_id为空时删除table1对应行
    DELETE FROM table1 t1
    USING params p
    WHERE p.new_id IS NULL AND t1.id = p.id
),
del_t2 AS (
    -- new_id为空时删除table2并返回结果
    DELETE FROM table2 t2
    USING params p
    WHERE p.new_id IS NULL AND t2.id = p.id
    RETURNING 'test'::VARCHAR(500) AS msg, '1'::BIT AS isSuccessful
),
ins_t1 AS (
    -- new_id不为空时插入table1并返回结果
    INSERT INTO table1(id, name)
    SELECT p.id, 'testname'
    FROM params p
    WHERE p.new_id IS NOT NULL
    RETURNING 'insert success'::VARCHAR(500) AS msg, '1'::BIT AS isSuccessful
)
-- 合并返回结果
SELECT * FROM del_t2
UNION ALL
SELECT * FROM ins_t1;

该需求是否必须创建函数才能完成？ #

不是，上述方案3的可写CTE方式就不需要创建任何函数，直接执行单条SQL即可拿到返回结果。如果你的逻辑比较复杂、需要多次复用，使用函数的可读性和可维护性会更高。

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