From: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
To: Christophe Pettus <xof@thebuild.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Subject: AW: Why is materialized view creation a "security-restricted operation"?
Date: Tue, 13 Oct 2026 07:00:50 +0000
Message-ID: <22203074e8c64768bc28133ac928bd9c@stmuk.bayern.de> (raw)
In-Reply-To: <0656242B-2228-420A-9652-33E21D404BF2@thebuild.com>
References: <77826d7580a9488ca28eb4bcfe94aa37@stmuk.bayern.de>
<2e9d29c1513afb47d652387ea5299076ef24adce.camel@cybertec.at>
<f69b47dbbe374bac8088887c21193f26@stmuk.bayern.de>
<9f30126c-9e72-4ced-83e2-3f8e5da183d3@aklaver.com>
<569054683acf4de7a3f2de80391b4e71@stmuk.bayern.de>
<81F7B946-997E-496E-BABC-514F9F0E9FFB@thebuild.com>
<9b969e375e504257b41fda27f140c165@stmuk.bayern.de>
<0FC732AF-86D8-42CB-986A-78D166FE9DA2@thebuild.com>
<dea673f597b2444e82f17f5383ba46ad@stmuk.bayern.de>
<0656242B-2228-420A-9652-33E21D404BF2@thebuild.com>
> 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@thebuild.com>
Gesendet: Dienstag, 13. Oktober 2026 08:55
An: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@postgresql.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.Faerber@stmuk.bayern.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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: Franz-Josef.Faerber@stmuk.bayern.de, xof@thebuild.com, adrian.klaver@aklaver.com
Subject: Re: AW: Why is materialized view creation a "security-restricted operation"?
In-Reply-To: <22203074e8c64768bc28133ac928bd9c@stmuk.bayern.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox