PostgreSQL存储过程内使用临时表提示relation不存在如何解决？

错误原因 #

PostgreSQL中LANGUAGE sql的存储过程会在创建阶段完成所有语句的静态语义解析，你代码中定义的临时表my_temp只有在存储过程实际运行时才会被创建，创建存储过程的阶段该表并不存在，因此解析INSERT、SELECT语句时会抛出关系不存在的错误。

解决方法 #

方案1：切换为PL/pgSQL语言编写（推荐） #

PL/pgSQL是PostgreSQL内置的过程化语言，采用运行时逐句解析的迟绑定逻辑，只要执行INSERT语句前临时表已经完成创建，就不会触发解析错误。
如果你的逻辑需要返回临时表的查询结果，建议改为PL/pgSQL函数实现：

CREATE OR REPLACE FUNCTION etl.my_test_function()
RETURNS TABLE (var1 VARCHAR(255), var2 VARCHAR(255))
LANGUAGE plpgsql
AS 
$$
BEGIN
   CREATE TEMP TABLE IF NOT EXISTS my_temp(
       var1 VARCHAR(255),
       var2 VARCHAR(255)
   ) ON COMMIT DROP;
    
   INSERT INTO my_temp (var1, var2)
   SELECT table_schema, column_name
   FROM information_schema.columns;

   RETURN QUERY SELECT * FROM my_temp;
END;
$$;

调用方式为：
SELECT * FROM etl.my_test_function();

方案2：SQL语言存储过程使用动态SQL #

如果必须保留LANGUAGE sql的写法，可以将依赖临时表的语句改为EXECUTE动态执行，避开创建阶段的静态解析：

CREATE OR REPLACE PROCEDURE etl.my_test_procedure()
LANGUAGE sql
AS 
$$
   CREATE TEMP TABLE IF NOT EXISTS my_temp(
       var1 VARCHAR(255),
       var2 VARCHAR(255)
   ) ON COMMIT DROP;
    
   EXECUTE 'INSERT INTO my_temp (var1, var2)
   SELECT table_schema, column_name
   FROM information_schema.columns';

   -- 注意SQL存储过程无法直接返回SELECT结果，如需返回结果仍建议改用函数
   EXECUTE 'SELECT * FROM my_temp';
$$;

注意事项 #

你定义的临时表指定了ON COMMIT DROP，存储过程如果在独立事务中调用，事务结束后临时表会自动删除，符合中间数据暂存的使用场景。

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