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

On 10/13/26 9:23 AM, Adrian Klaver wrote:
> On 10/13/26 4:36 AM, Färber, Franz-Josef (StMUK) wrote:
>>>> [rant about PostgreSQL security restrictions]
>> No no no, not about security restrictions. The rant is about lacking
>> orthogonality. About often NOT being able to combine language
>> features. Doesn't anybody share that view? We COULD do better, I think.
>
> Quick and dirty example.
>
> cat test.csv
> 1,dog,t
> 2,cat,f
> 3,fish,t
>
> CREATE OR REPLACE FUNCTION public.temp_table()

Forgive the function name, it was created before I realized I was not
really going with a temporary table.

>  RETURNS TABLE(id integer, type character varying, active boolean)
>  LANGUAGE plpgsql
> AS $function$
> BEGIN
>     DROP TABLE IF EXISTS test_csv;
>     CREATE TABLE test_csv(id integer, type varchar, active boolean);
>     COPY test_csv FROM '/var/lib/postgresql/18/main/test.csv' WITH CSV;
>     RETURN QUERY EXECUTE
>         'SELECT id, type, active FROM test_csv';
> END;
> $function$
>
>
> select * from temp_table();
>  id | type | active
> ----+------+--------
>   1 | dog  | t
>   2 | cat  | f
>   3 | fish | t
>
>
>>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com