Re: Why is materialized view creation a "security-restricted operation"?

On Thu, 2026-10-01 at 13:11 +0000, Färber, Franz-Josef (StMUK) wrote:
> some questions about this 9-year-old post below.
> [ https://postgr.es/m/flat/CAFBoRzf6HwFg1jovdOrbtC6x4xKV__-t5EjSzbY2068S01pcTg%40mail.gmail.com ]
>
> I also stumbled over a similar case as the failing
>
> CREATE MATERIALIZED VIEW some_view AS SELECT * FROM my_func();
>
> . where my_func tries to create a temp table.
>
> When writing an arbitrarily complex function my_func, I claim there are cases when you want
> to store intermediate results into variables. And what if the intermediate results are tables?
> Well, Postgres/plpgsql does not support table-valued variables, so the next best choice are temp tables.
> But here we have: Creating temp tables is forbidden inside a mat view, see the mail below.
> Because we might have a side effect ("change of seesion state"): The creation of this very temp table.
>
> What to do now? Well it turns out I actually CAN create a NON-temp table. Is that what you want me
> to do? Really? Isn't this the bigger side effect: Creating a table?
>
> It actually does not make sense to me, restricting one effect, while allowing the much bigger effect.

Creating a temporary table and creating a permanent table are not the same thing:

- it requires different permissions: TEMP on the database (which is granted to PUBLIC
by default) and CREATE on a schema (which only the owner has by default)

- the temporary schema by default is at the beginning of the search_path, so it can
easily shadow objects in other schemas

> * What I actually needed is a table-valued variable. One I can use inside my function. Which shall
> also be local/unique (i. e. not being used by concurrent users or sessions, or even in the call
> stack of the very same session).
>
> * The next best thing would be a temp table, local/unique in the sense as above, that gets destroyed
> when leaving the function.
> Invent some CREATE TEMP TABLE . ON EXIT FUNCTION DROP?
> (.and wouldn't that be quite equivalent to table-valued variables?)
>
> Any suggestions?

If you use a permanent table instead, I recommend an UNLOGGED table.

Other than that, you could use a variable that is an array of table rows
(declared as my_table[]).

Yours,
Laurenz Albe