Thanks for the suggestion.
But what exactly is the suggestion?
At least I understand DuckDB can directly read without having to craete a temp
table:
* SELECT * FROM 'test.csv.gz';
* SELECT * FROM read_csv('test.csv', header = false);
... which is what Postgres is missing.
Regards,
Franz-Josef Färber
-----Ursprüngliche Nachricht-----
Von: Adrian Klaver <[email protected]>
Gesendet: Montag, 12. Oktober 2026 18:17
An: Färber, Franz-Josef (StMUK) <[email protected]>; Laurenz
Albe <[email protected]>; [email protected]
Cc: Haupt, Matthias (StMUK) <[email protected]>
Betreff: Re: AW: Why is materialized view creation a "security-restricted
operation"?
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__-t5EjSzbY2068
>> S
>> 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
