我可以在同一个SQL函数中创建和访问表吗?

Question

我正在尝试创建一个Postgres sql函数，它为我的数据库运行一些例程。

sql函数调用plpgsql函数，该函数创建了几个临时表，但不返回任何东西(RETURNS void)。在我的sql函数中应该使用plpgsql函数创建的一个表。

CREATE OR REPLACE FUNCTION public.my_sql_function()
RETURNS text AS
$BODY$
select public.my_plpsql_function(); -- this returns void, but has created a temp table "tmp_tbl"
DROP TABLE IF EXISTS mytable CASCADE;
CREATE TABLE mytable (
skov_id int8 PRIMARY KEY,
skov_stor int4,
skov_areal_ha numeric,
virkningfra timestamp(0) without time zone,
plannoejagtighed float8,
vertikalnoejagtighed float8,
geom geometry(MultiPolygon,25832),
orig_geom geometry(Polygon, 25832) 
);
INSERT INTO mytable
select * from tmp_tbl ....
$BODY$  LANGUAGE sql;

当我尝试运行这些行时，我得到以下错误:

ERROR: relation "tmp_tbl" does not exist

pgAdmin将select * from tmp_tbl ...下划线作为错误部分。

所以SQL-function不会注意到plpsql-function已经创建了一个临时表。

有解决方法吗?

Answer 1

创建和在同一个SQL函数中访问表通常是不可能的。无论是在SQL函数中直接创建表还是在嵌套函数调用中创建表都没有区别。所有的对象必须是可见的

在这一章的开头查询语言(SQL)函数在手册中指出:

注意

在解析SQL函数之前解析整个函数体执行。而SQL函数可以包含更改属性的命令系统目录(例如，CREATE TABLE)，这些命令的效果的后续命令的解析分析期间将不可见函数。因此，例如，如果将CREATE TABLE foo (...); INSERT INTO foo VALUES(...);打包成一个单独的，将不能像期望的那样工作SQL函数，因为当INSERT命令存在时，foo还不存在解析。建议使用PL/pgSQL而不是SQL函数这种情况

相关:

• 为什么PL/pgSQL函数有副作用，而SQL函数没有?
• PostgreSQL函数中sql语言与plpgsql语言的区别

Answer 2

我认为这是不可能的-至少在未来的版本中不可能。SQL函数类似于视图，因此对数据库对象的引用在函数创建时应该是有效的。

没有任何解决方法-如果您需要临时表，请使用PLpgSQL，或者尝试在没有临时表的情况下编写代码(它可以更好)。