Re: Dropping a temporary view?

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