postgresql - 使用临时表的函数的物化 View

Question

我有一个存储过程，它在其主体中使用了一个临时表。尝试使用此过程创建物化 View ，例如

CREATE MATERIALIZED VIEW my_view AS SELECT * FROM my_function;

给我一​​个错误:

ERROR:  cannot create temporary table within security-restricted operation

在源码的注释中有解释:

/*
 * Security check: disallow creating temp tables from security-restricted
 * code.  This is needed because calling code might not expect untrusted
 * tables to appear in pg_temp at the front of its search path.
 */

除了修改存储过程本身不使用临时表之外，是否有其他解决方法？

Answer 1

此限制背后的想法是防止在刷新实体化 View 后更改 session 状态。这实际上在您从源代码添加的注释中进行了解释(尽管它可能会造成混淆)。

换句话说，这意味着查询可能会选择一个新的临时表(您可以在函数中创建它)，即使已经存在同名的常规表也是如此。 pg_temp 架构在查找表时隐式添加到 SEARCH PATH 中。

---

我可以想到两种解决方法:

1. 使用普通表而不是临时表。您可以指定 UNLOGGED 以获得更好的性能。

示例代码:

CREATE FUNCTION my_function()
RETURNS BOOLEAN
LANGUAGE 'plpgsql'
AS '
BEGIN
    DROP TABLE IF EXISTS my_tmp_table;
    CREATE UNLOGGED TABLE my_tmp_table(a int); -- regular unlogged table
    RETURN TRUE;
END';

CREATE MATERIALIZED VIEW my_view AS SELECT * FROM my_function(); -- materialized view

1. 创建一个常规表而不是物化 View ，然后在需要刷新时删除并重新创建它

示例代码:

CREATE FUNCTION my_function()
RETURNS BOOLEAN
LANGUAGE 'plpgsql'
AS '
BEGIN
    DROP TABLE IF EXISTS my_tmp_table;
    CREATE TEMP TABLE my_tmp_table(a int); -- temp table
    RETURN TRUE;
END';

CREATE TABLE my_view AS SELECT * FROM my_function(); -- table, not a view