PostgreSQL返回JSON的函数中RETURN语句对临时表删除的影响

核心原理 #

• PL/pgSQL 中普通RETURN语句的作用是立即终止函数运行，将结果返回给调用方，RETURN之后的所有代码都不会被执行。
• RETURN QUERY的作用是将指定查询的结果追加到函数的输出结果集中，它不会中断函数执行，后续代码会继续正常运行。

问题原因解析 #

第二个函数报错的原因 #

你写的test_json函数执行到RETURN '{"name": "Moises"}'::JSON;时就直接退出了，后面的DROP TABLE temp_test_json从未被运行，临时表会一直保留在当前会话的临时模式中，第二次执行函数时创建同名表就会抛出"表已存在"的错误。

第一个函数正常运行的原因 #

test_table函数中的RETURN QUERY执行后不会退出函数，后续的DROP TABLE temp_test_table会正常执行，临时表每次调用都会被清理，因此多次执行不会报错。

修复方案 #

如果需要使用普通RETURN返回结果，只需要将删表逻辑调整到RETURN语句之前即可，示例如下：

CREATE OR REPLACE FUNCTION test_json()
   RETURNS JSON
AS $$
BEGIN
    CREATE TEMP TABLE temp_test_json AS SELECT * FROM Users;
    -- 此处编写需要使用temp_test_json的业务逻辑
    DROP TABLE temp_test_json; -- 先清理临时表再返回结果
    RETURN '{"name": "Moises"}'::JSON;
END; $$
LANGUAGE plpgsql;

如果想要更高的容错性，避免业务逻辑报错导致临时表未清理，可以添加异常块兜底：

CREATE OR REPLACE FUNCTION test_json()
   RETURNS JSON
AS $$
BEGIN
    CREATE TEMP TABLE IF NOT EXISTS temp_test_json AS SELECT * FROM Users;
    -- 业务逻辑
    DROP TABLE IF EXISTS temp_test_json;
    RETURN '{"name": "Moises"}'::JSON;
EXCEPTION
    WHEN OTHERS THEN
        DROP TABLE IF EXISTS temp_test_json;
        RAISE; -- 重新抛出原有异常
END; $$
LANGUAGE plpgsql;

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