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__-t5EjSzbY2068S01pcTg%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
