PostgreSQL函数中的临时表

Question

我找不到一个清晰的说明如何创建(和使用)表格来进行函数内部计算的语法。请问有人可以给我提供一个语法例子吗？

根据我找到的资料，我尝试了这个（带有和不带有@在temp_table之前）：

CREATE FUNCTION test.myfunction()
RETURNS SETOF test.out_table
AS $$

DECLARE @temp_table TABLE
( 
        id int,
        value text
 )
BEGIN
 INSERT INTO @temp_table 
        SELECT id, value
        FROM test.another_table;

 INSERT INTO test.out_table
        SELECT id, value
        FROM @temp_table;
RETURN END
$$ LANGUAGE SQL;

我得到：

错误：第5行的“DECLARE”附近有语法错误 LINE 5: DECLARE @temp_table TABLE

-

我还尝试了这里建议的使用CREATE TABLE方法，如下：

CREATE FUNCTION test.myfunction()
RETURNS SETOF test.out_table
AS $$

    CREATE TABLE temp_table AS
        SELECT id, value
        FROM test.another_table;

    INSERT INTO test.out_table
        SELECT id, value
        FROM temp_table;

$$ LANGUAGE SQL;

我得到了以下结果：

错误：关系“temp_table”不存在 第11行：从temp_table查询

（显然，我知道在上面的代码中不需要temp_table，但这不是重点：）=>我想理解语法以使其正常工作）

- François M.

---

Postgres使用临时表来实现此目的。表变量是SQL Server的一个特性。 - Gordon Linoff

你在手册的哪个部分找到了语法DECLARE @temp_table TABLE...？ - user330315

Answer 1

创建临时表的适当语法为：

create temp table...

但是在退出函数之前，一定要确保删除临时表。此外，我建议使用以下语法：

CREATE TEMP TABLE IF NOT EXISTS temp_table AS
    SELECT id, value
    FROM test.another_table;

因此，您的函数将会像这样：

CREATE FUNCTION test.myfunction()
RETURNS SETOF test.out_table
AS $$

    CREATE TEMP TABLE IF NOT EXISTS temp_table AS
        SELECT id, value
        FROM test.another_table;

    INSERT INTO test.out_table
        SELECT id, value
        FROM temp_table;

DROP TABLE temp_table;

$$ LANGUAGE SQL;

但如果我可以这么说，我想要重写这个函数，使它更加正确：

CREATE FUNCTION test.myfunction()
RETURNS TABLE (id int, value varchar) -- change your datatype as needed
AS $$
BEGIN;
CREATE TEMP TABLE IF NOT EXISTS temp_table AS
    SELECT id, value
    FROM test.another_table;

INSERT INTO test.out_table
    SELECT id, value
    FROM temp_table;

DROP TABLE temp_table;

RETURN QUERY 
SELECT id, value
from temp_table;

END;
$$ LANGUAGE plpgsql;

未经测试；如果出现问题，请告诉我。

- dizzystar

---

明天我会在工作中测试它并告诉你结果（如果它有效，我会接受它），同时，我只是想感谢你抽出时间 :) - François M.

我因为CREATE语句而得到了一个语法错误。更确切地说，我得到了以下错误信息：ERROR: syntax error at or near "create" // LINE 4: create TEMP TABLE temp_mag_ref_compl AS // ********** Erreur ********** // ERROR: syntax error at or near "create" // État SQL :42601 // Caractère : 114 - François M.

为什么你说在退出函数之前一定要删除临时表？文档中有提到吗？如果不删除会发生什么？ - Wildcard

@Wildcard 如果你保持事务是开启的，那么临时表会一直存在，从而抛出“表已经存在”的错误。 - dizzystar

@dizzystar，您似乎正在删除临时表，然后在执行RETURN QUERY之前选择它...这是正确的吗？它不应该不存在吗？ - Dan