Materialized view using a function using a temporary table

Question

I have a stored procedure, which uses a temporary table in its body. Trying to create a materialized view using this procedure, like

CREATE MATERIALIZED VIEW my_view AS SELECT * FROM my_function;

Gives me an error:

ERROR:  cannot create temporary table within security-restricted operation

It is explained in a comment in the source code:

/*
 * 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.
 */

Is there a workaround other than reworking the stored procedure itself not to use temporary tables?

Answer 1

The idea behind this restriction is to prevent changes of session state after refreshing materialized view. This is actually explained in the comment you've added from the source code (though it might be confusing).

In other words it means that a new temporary table (which you could create within a function) might be picked up by the query even though a regular table with the same name already exists. pg_temp schema is implicitly added into SEARCH PATH when looking for tables.

---

There are two workarounds that I can think of:

1. use normal table instead of a temporary table. You can specify UNLOGGED for better performance.

Example code:

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

2. instead of materialized view, create a regular table and then drop and recreate it whenever a need for refresh occurs

Example code:

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