Postgres中存储过程中的临时表

Question

我正在尝试在存储过程中创建和填充临时表，以保存我正在处理的数据的一些中间状态。

我已经创建了一个示例代码来解释我要做什么：

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;
    
   INSERT INTO my_temp (
       var1,
       var2
   )
   SELECT 
       table_schema,
       column_name
   FROM information_schema.columns;

   SELECT 
        *
   FROM my_temp
$$

在尝试创建此存储过程时，数据库返回以下错误消息： ERROR: relation "my_temp" does not exist LINE 10: INSERT INTO my_temp ( ^ SQL state: 42P01 Character: 171

注：我的Postgres版本是13.3

- Antjes

---

临时表完全没有用处。只需返回选择的结果即可。 - user330315

Answer 1

你需要使用plpgsql而不是sql

CREATE OR REPLACE FUNCTION my_test_procedure()
RETURNS TABLE(var1 VARCHAR(255), var2 VARCHAR(255))
AS 
$$
 DECLARE

  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;
$$ LANGUAGE plpgsql;

- JGH

---

非常感谢！只是一个小的后续。实际上，我并不希望从过程（函数？）中获得该表作为输出，而是将其用作获取另一个中间结果的输入，直到最后一行将被插入到最终表中。所以，我猜想，RETURNS 应该是空的（RETURNS void），对吗？ 另外，为什么你使用 FUNCTION 而不是 PROCEDURE？有没有任何文档可以帮助我理解这个问题？ 附注：我的背景是 SqlServer，在那个环境下这个问题要简单得多... - Antjes

Answer 2

错误的原因是SQL函数在创建时进行解析。您可以通过将参数check_function_bodies设置为off来避免这种情况。

但这并不能帮助您太多：它允许您创建该函数，但当您执行该过程时，您仍将遇到相同的错误，因为所有语句都在函数开始时解析，而此时my_temp不存在。

解决方案是使用PL/pgSQL，就像JGH的答案建议的那样。

- Laurenz Albe