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

> It doesn't seem much more work to create a foreign table than to create a temp table in a PL/pgSQL function.
Because contrary to a function a foreign table is not parametrizable, I claimed I would have to create multiple foreign tables - compared to just one function.

Concerning the DRY (don't repeat yourself) principle, I thought a function would be the better choice.

As I somewhere said, I miss modularity / orthogonality in SQL / Postgres. We already mentioned multiple places in this thread. This is the recurring theme here.

Regards,
Franz-Josef Färber

-----Ursprüngliche Nachricht-----
Von: Christophe Pettus <xof(at)thebuild(dot)com>
Gesendet: Dienstag, 13. Oktober 2026 08:55
An: Färber, Franz-Josef (StMUK) <Franz-Josef(dot)Faerber(at)stmuk(dot)bayern(dot)de>
Cc: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>; pgsql-general(at)postgresql(dot)org
Betreff: Re: Why is materialized view creation a "security-restricted operation"?

> On Oct 12, 2026, at 23:54, Färber, Franz-Josef (StMUK) <Franz-Josef(dot)Faerber(at)stmuk(dot)bayern(dot)de> wrote:
> * ... the PostgreSQL COPY ... FROM PROGRAM statement could be used like a query, without having to use a temp table. Due to typing, one would have to use a FROM clause with column names and types defined, just like tablefunc: https://www.postgresql.org/docs/current/tablefunc.html
> * ... or there would be some chance to create a temp table inside a plpgsql function ... which in the end is used in my use case inside a materialized view ... which is forbidden and which can still be seen in the subject of this mail as the first question I had.

It doesn't seem much more work to create a foreign table than to create a temp table in a PL/pgSQL function.