sql - postgres 无法识别函数中的临时表

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

postgres 不支持函数中的临时表吗？或者是否有一些我遗漏的语法东西让我感到窒息并且它给我一个虚假错误？

Answer 1

Postgres 对您尝试创建的函数运行一些简单的检查，它发现(正确地)表 work_list 不(还)存在。我看到两个选项:

1. “假装它直到你成功”

在创建函数之前实际创建(临时)表。临时表将在 session 结束时消失，但是一旦函数被创建，你就已经通过了这个测试。
显然，在同一 session 中运行该函数之前，您必须删除该表以避免冲突。不过更好:使用 CREATE TEMP TABLE IF NOT EXISTS在你的函数中(Postgres 9.1+)。如果表已经存在，您可能想要截断它......

但是(见下面的评论)，引用手册

The entire body of a SQL function is parsed before any of it is executed. While a SQL function can contain commands that alter the system catalogs (e.g., CREATE TABLE), the effects of such commands will not be visible during parse analysis of later commands in the function. Thus, for example, CREATE TABLE foo (...); INSERT INTO foo VALUES(...); will not work as desired if packaged up into a single SQL function, since foo won't exist yet when the INSERT command is parsed. It's recommended to use PL/pgSQL instead of a SQL function in this type of situation.

大胆强调我的。

2。改为使用 PL/pgSQL

plpgsql 中的检查不太彻底。如果 Postgres 仍然提示(在本例中没有)，你也可以 execute SQL dynamically with EXECUTE .

旁白:在许多情况下，附近有一个没有临时表的更高效的解决方案......