On 2024-03-20 15:58 +0100, Celia McInnis wrote: > I am using postresql 16, am trying to use temporary views in a piece of > software that I am writing, and would like it to be able to drop and > recreate temporary views. It seems from the documentation that I can only > use "CREATE OR REPLACE TEMPORARY VIEW" if the replacement view has the same > columns, so Is there a correct way to drop a temporary view? > > I can create a temporary view, but get a syntax error when I do what I > thought would drop it. Here is a simple example of what doesn't work: > > tt=# create temporary view tempview as select now() as junk; > CREATE VIEW > tt=# select * from tempview; > junk > ------------------------------- > 2024-03-20 14:21:27.441168+00 > (1 row) > > tt=# drop temporary view tempview; > ERROR: syntax error at or near "temporary" > LINE 1: drop temporary view tempview; > ^ It's just DROP VIEW for normal and temporary views. > Also, when I then tried (I formerly had a non-temporary view called > tempview) > > DROP VIEW tempview; > DROP VIEW > > postgresql did that successfully, but when I then did > > select * from tempview: > > postgresql hung for a long time (more than 7 minutes) before returning the > contents of some previous view tempview (a previous (temporary, I guess) > view by that name that was created by my software when I was not creating a > temporary view?). I really wasn't expecting this, so if someone can > explain, that would be great. The first view must have been a regular (non-temporary) one. It is then possible to create a temporary view of the same name that shadows the original view if pg_temp is searched first, which is the default if you haven't modified search_path. But it's not possible to create a second temporary view of the same name because they live in the same namespace (pg_temp_N): regress=# create view tempview as select 1 a; CREATE VIEW regress=# select * from tempview; a --- 1 (1 row) regress=# create temp view tempview as select 2 b; CREATE VIEW regress=# select * from tempview; b --- 2 (1 row) regress=# create temp view tempview as select 3 c; ERROR: relation "tempview" already exists regress=# select * from tempview; b --- 2 (1 row) regress=# drop view tempview; DROP VIEW regress=# select * from tempview; a --- 1 (1 row) -- Erik
