On 10/12/26 7:59 AM, Färber, Franz-Josef (StMUK) wrote:
Sorry for my late reply. Thanks for pointing some stuff out.
Other than that, you could use a variable that is an array of table rows
(declared as my_table[]).
A bit clumsy but yes, thank you.
I appreciate PostgreSQL, but what sometimes baffles me is that SQL (or is it
PostgreSQL) is ... what should I call it? Lacking modularity / orthogonality?
Simply put: When you want to combine different parts, you will meet obstacles.
More obstacles than one is accustomed to when coming from other languages.
Imperative or functional programming languages, that is.
If I need some temporary space for structured data and am restricted to avoid a
temp table; if I am then forced to use a global table (which not only can be
seen in other connections, but might introduce concurrency issues). Then work
around using arrays. If even applicable.
Because what I haven't said yet and should admit now, the temp table gets
filled with COPY ... FROM PROGRAM.
The next obstacle which occurs strangely to me is that COPY ... FROM PROGRAM
only can dump data into tables. One cannot use it like other table-like
objects, like a view, One cannot COPY ... FROM PROGRAM into an array.
Postgres offers again no feasible way for what I was trying to archieve.
Ok, I think some people get the point that I claim SQL / Postgres is lacking
modularity / orthogonality in the way as I tried to define above.
And I think some people want to dive into my special use case, perhaps consider
it as kind-of-insane. So let me explain. And perhaps somebody can give good
advice.
Something like this:
cat test.csv
id,type,active
1,dog,t
2,cat,f
3,fish,t
duckdb
DuckDB v1.4.4 (Andium) 6ddac802ff
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D
D create temp table test_tbl as select * from 'test.csv';
D select * from test_tbl;
┌───────┬─────────┬─────────┐
│ id │ type │ active │
│ int64 │ varchar │ boolean │
├───────┼─────────┼─────────┤
│ 1 │ dog │ true │
│ 2 │ cat │ false │
│ 3 │ fish │ true │
└───────┴─────────┴─────────┘
Not exactly sure how this might fit into your scheme, see below for more
information:
https://duckdb.org/docs/current/data/overview
https://duckdb.org/docs/current/data/csv/overview
https://duckdb.org/docs/current/clients/overview
https://duckdb.org/docs/current/core_extensions/postgres/overview
* I am running Postgres in a container.
* I am using https://github.com/pramsey/pgsql-http to query REST services using
credentials.
* I took it as good advice to pass environment variables into the container,
and to pass passwords as single files, in mounted container directory
/run/secrets/ .
* Inside postgres I am trying to load that data, using memoization pattern. For
example using CREATE TEMP TABLE x ...; COPY x ... FROM PROGRAM 'cat
/run/secrets/mypassword'. This part is encapsulated as a function.
* Now there was one point where I was using a MATERIALIZED VIEW accessing that
function. And well, that failed.
What I COULD do is replace COPY x ... FROM PROGRAM with a file_fdw FOREIGN
TABLE, but this way it isn't parametrizable. I would have to create as many
foreign tables as I have passwords / environment variables, I think.
Regards,
Franz-Josef Färber
-----Ursprüngliche Nachricht-----
Von: Laurenz Albe <[email protected]>
Gesendet: Donnerstag, 1. Oktober 2026 22:13
An: Färber, Franz-Josef (StMUK) <[email protected]>;
[email protected]
Cc: Haupt, Matthias (StMUK) <[email protected]>
Betreff: 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__-t5EjSzbY2068S
01pcTg%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
