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