Ok, thanks - so I guess that means that if there is both a temporary and a non temporary view called "tempvie",
DROP VIEW tempview; will remove the 1st tempview found, which with my path is the temporary one. Is there some reason why it then took 7 minutes to select from the non-temporary view tempview after I dropped the temporary view tempview? I have sometimes had some very long query times when running query software, and maybe they are resulting from my switching between temporary and non-temporary views of the same name while debugging. If so, is there something I should be doing to clean up any temporary messes I am creating? Thanks, Celia McInnis On Wed, Mar 20, 2024 at 11:12 AM Erik Wienhold <e...@ewie.name> wrote: > 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 >
