PostgreSQL无法识别函数中的临时表

Question

这可能是因为我很累，或者我对postgres不太熟悉。但是，我试图在函数中使用临时表，而postgres抱怨“关系不存在”。然而，如果我取出函数体并执行它，它就能正常工作。下面是我尝试创建的函数类型样本。请记住，我已经剥离了一切有趣的东西，以使其接近最小值以显示我的问题。

CREATE OR REPLACE FUNCTION dbo.somefunc() RETURNS void AS
$BODY$ 

CREATE TEMPORARY TABLE work_list
(
    name text,
    level smallint
);

insert into work_list
    (name, level)
values
    ('someone', 25);

$BODY$
LANGUAGE sql VOLATILE;

我收到的投诉与插入语句有关。实际上，投诉的内容是：

ERROR:  relation "work_list" does not exist

PostgreSQL是否不支持在函数中使用临时表？还是我缺少某些语法，导致出现了错误信息？

- OpenIdsAreDumb

Answer 1

Postgres运行一些简单的检查来创建你正在尝试创建的函数，并正确地发现表work_list不存在（但尚未存在）。我看到有两个选项：

1. 先生造它，直到你创造它为止

在创建函数之前实际上创建(临时)表。 会话结束后，临时表将消失，但是一旦创建了函数，你就可以永久通过此测试。
显然，您必须在同一会话中运行函数之前删除该表以避免冲突。 更好的方法是在函数中使用CREATE TEMP TABLE IF NOT EXISTS（Postgres 9.1+）。 如果已经存在该表，则可以截断该表...

然而（请参见下面的评论），引用手册

在执行任何命令之前，整个SQL函数体都将被解析。 虽然SQL函数可以包含更改系统目录的命令（例如，CREATE TABLE），但这些命令的影响不会在函数中稍后的命令的解析分析期间可见。 因此，例如，CREATE TABLE foo (...); INSERT INTO foo VALUES(...);如果打包成一个单独的SQL函数，则不会按预期工作，因为在解析INSERT命令时还不存在foo。 建议在这种情况下使用PL/pgSQL而不是SQL函数。

加粗强调属于我自己。

2. 改用PL/pgSQL

plpgsql的检查程度较低。如果Postgres仍然抱怨（在这种情况下没有），你也可以使用EXECUTE动态执行SQL。

另外：在许多情况下，有一个更高效的解决方案，无需使用临时表...

- Erwin Brandstetter

---

“假装”方法真的可行吗？这就是我为什么要问的原因。 - user533832

@JackDouglas：重新测试后，我发现SQL函数似乎忽略了函数内创建的临时表。最好使用plpgsql来解决这个问题。更多信息请参见你在dba.SE上的新问题。 - Erwin Brandstetter

如果您将函数定义放在迁移脚本中，您可以使用SET check_function_bodies = false;来防止在函数加载期间出现关系不存在错误。 - Guy

@Guy：虽然这样可以避免在函数创建时出现错误消息，但在执行时仍会出现错误。我已经在上面添加了详细信息。 - Erwin Brandstetter

Answer 2

将这两个语句合并。通过执行“select into”类型的语法来创建临时表，这样就可以做到。 CREATE TEMP TABLE SomeTable AS SELECT * FROM OtherTable ;

- OcRob

---

如果您需要在该函数后面从SomeTable中进行选择操作，您仍将得到“ERROR: relation "SomeTable" does not exist”的错误提示。 - scw

对于@scw的评论，这应该如何工作...？ - Douglas Gaskell