How to drop a temporary view?

On Fri, 20 Apr 2012 17:36:59 +0200
Vincenzo Romano <vincenzo.romano@notorand.it> wrote:

Hi all.
I'd like use a temporary view "to hide" a non-temp one for some
queries. Later I'd need to drop that view in order to "revert to
normal operations". As there is no "DROP TEMPORARY VIEW ..." I'd be
forced to "CREATE OR REPLACE TEMPORARY VIEW ..." in order to
"overwrite" the temporary one with the same code as the non-temporary.
All this sounds quite complex to me. Is there a way to drop temporary
stuff before closing the session?
TIA.

DROP VIEW <view>

It will destroy the view, temporary or not...

Regards

Rodrigo

On Fri, Apr 20, 2012 at 10:36 AM, Vincenzo Romano
<vincenzo.romano@notorand.it> wrote:

Hi all.
I'd like use a temporary view "to hide" a non-temp one  for some queries.
Later I'd need to drop that view in order to "revert to normal operations".
As there is no "DROP TEMPORARY VIEW ..." I'd be forced to "CREATE OR
REPLACE TEMPORARY VIEW ..." in order to "overwrite" the temporary one
with the same code as the non-temporary.
All this sounds quite complex to me. Is there a way to drop temporary
stuff before closing the session?
TIA.

you can do it with vanilla DROP VIEW:

postgres=# create temp view t as select 1;
CREATE VIEW
postgres=# drop view t;
DROP VIEW

merlin

2012/4/20 Merlin Moncure <mmoncure@gmail.com>:

On Fri, Apr 20, 2012 at 10:36 AM, Vincenzo Romano
<vincenzo.romano@notorand.it> wrote:

Hi all.
I'd like use a temporary view "to hide" a non-temp one  for some queries.
Later I'd need to drop that view in order to "revert to normal operations".
As there is no "DROP TEMPORARY VIEW ..." I'd be forced to "CREATE OR
REPLACE TEMPORARY VIEW ..." in order to "overwrite" the temporary one
with the same code as the non-temporary.
All this sounds quite complex to me. Is there a way to drop temporary
stuff before closing the session?
TIA.

you can do it with vanilla DROP VIEW:

postgres=# create temp view t as select 1;
CREATE VIEW
postgres=# drop view t;
DROP VIEW

merlin

Ok. That works. How can I know if there's a temporary view with the
same name in my session?

On Fri, Apr 20, 2012 at 10:51 AM, Vincenzo Romano
<vincenzo.romano@notorand.it> wrote:

2012/4/20 Merlin Moncure <mmoncure@gmail.com>:

On Fri, Apr 20, 2012 at 10:36 AM, Vincenzo Romano
<vincenzo.romano@notorand.it> wrote:

Hi all.
I'd like use a temporary view "to hide" a non-temp one  for some queries.
Later I'd need to drop that view in order to "revert to normal operations".
As there is no "DROP TEMPORARY VIEW ..." I'd be forced to "CREATE OR
REPLACE TEMPORARY VIEW ..." in order to "overwrite" the temporary one
with the same code as the non-temporary.
All this sounds quite complex to me. Is there a way to drop temporary
stuff before closing the session?
TIA.

you can do it with vanilla DROP VIEW:

postgres=# create temp view t as select 1;
CREATE VIEW
postgres=# drop view t;
DROP VIEW

merlin

Ok. That works. How can I know if there's a temporary view with the
same name in my session?

well, arguably you should already know somehow. but if you don't,
query information_schema.views for a table_name with a table_schema
LIKE 'pg_temp%'.

merlin

Merlin Moncure <mmoncure@gmail.com> writes:

On Fri, Apr 20, 2012 at 10:51 AM, Vincenzo Romano
<vincenzo.romano@notorand.it> wrote:

Ok. That works. How can I know if there's a temporary view with the
same name in my session?

well, arguably you should already know somehow. but if you don't,
query information_schema.views for a table_name with a table_schema
LIKE 'pg_temp%'.

Not sure that is safe --- won't the info schema also show temp views
of other sessions?

You might be able to use "DROP VIEW pg_temp.foo", which will either
drop a temp view of your own session or throw an error if there is none.

regards, tom lane

2012/4/20 Tom Lane <tgl@sss.pgh.pa.us>:

Merlin Moncure <mmoncure@gmail.com> writes:

On Fri, Apr 20, 2012 at 10:51 AM, Vincenzo Romano
<vincenzo.romano@notorand.it> wrote:

Ok. That works. How can I know if there's a temporary view with the
same name in my session?

well, arguably you should already know somehow.  but if you don't,
query information_schema.views for a table_name with a table_schema
LIKE 'pg_temp%'.

Not sure that is safe --- won't the info schema also show temp views
of other sessions?

You might be able to use "DROP VIEW pg_temp.foo", which will either
drop a temp view of your own session or throw an error if there is none.

                       regards, tom lane

Cool! It works, despite my temporary schema should be now pg_temp_29!
It's at chapter 18.11.1 (my fault for not searching enough).

Thanks a lot Tom.

2012/4/20 Vincenzo Romano <vincenzo.romano@notorand.it>:

2012/4/20 Tom Lane <tgl@sss.pgh.pa.us>:

Merlin Moncure <mmoncure@gmail.com> writes:

On Fri, Apr 20, 2012 at 10:51 AM, Vincenzo Romano
<vincenzo.romano@notorand.it> wrote:

Ok. That works. How can I know if there's a temporary view with the
same name in my session?

well, arguably you should already know somehow.  but if you don't,
query information_schema.views for a table_name with a table_schema
LIKE 'pg_temp%'.

Not sure that is safe --- won't the info schema also show temp views
of other sessions?

You might be able to use "DROP VIEW pg_temp.foo", which will either
drop a temp view of your own session or throw an error if there is none.

                       regards, tom lane

Cool! It works, despite my temporary schema should be now pg_temp_29!
It's at chapter 18.11.1 (my fault for not searching enough).

Thanks a lot Tom.

It works only if you have created at least one temporary object.
Until that the pg_temp "meta schema" doesn't resolve to a real schema
and the exception is thrown!
It's a cool thing anyway!

Vincenzo Romano <vincenzo.romano@notorand.it> writes:

2012/4/20 Vincenzo Romano <vincenzo.romano@notorand.it>:

2012/4/20 Tom Lane <tgl@sss.pgh.pa.us>:

You might be able to use "DROP VIEW pg_temp.foo", which will either
drop a temp view of your own session or throw an error if there is none.

It works only if you have created at least one temporary object.
Until that the pg_temp "meta schema" doesn't resolve to a real schema
and the exception is thrown!

I didn't say *which* error would get thrown ;-). If you are checking
that you'd need to be prepared for both cases.

regards, tom lane

On Fri, Apr 20, 2012 at 11:07 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Merlin Moncure <mmoncure@gmail.com> writes:

well, arguably you should already know somehow.  but if you don't,
query information_schema.views for a table_name with a table_schema
LIKE 'pg_temp%'.

Not sure that is safe --- won't the info schema also show temp views
of other sessions?

nope, it works (you only get to see your own temporary views
information_schema). i guess you could make an arguable case to fold
the temp schema name to a constant to hide the pg_temp_zz
implementation detail.

merlin

2012/4/20 Tom Lane <tgl@sss.pgh.pa.us>:

Vincenzo Romano <vincenzo.romano@notorand.it> writes:

2012/4/20 Vincenzo Romano <vincenzo.romano@notorand.it>:

2012/4/20 Tom Lane <tgl@sss.pgh.pa.us>:

You might be able to use "DROP VIEW pg_temp.foo", which will either
drop a temp view of your own session or throw an error if there is none.

It works only if you have created at least one temporary object.
Until that the pg_temp "meta schema" doesn't resolve to a real schema
and the exception is thrown!

I didn't say *which* error would get thrown ;-).  If you are checking
that you'd need to be prepared for both cases.

                       regards, tom lane

... WHEN OTHERS THEN NULL

should be pretty safe to DROP a VIEW!

2012/4/20 Vincenzo Romano <vincenzo.romano@notorand.it>:

2012/4/20 Tom Lane <tgl@sss.pgh.pa.us>:

Vincenzo Romano <vincenzo.romano@notorand.it> writes:

2012/4/20 Vincenzo Romano <vincenzo.romano@notorand.it>:

2012/4/20 Tom Lane <tgl@sss.pgh.pa.us>:

You might be able to use "DROP VIEW pg_temp.foo", which will either
drop a temp view of your own session or throw an error if there is none.

It works only if you have created at least one temporary object.
Until that the pg_temp "meta schema" doesn't resolve to a real schema
and the exception is thrown!

I didn't say *which* error would get thrown ;-).  If you are checking
that you'd need to be prepared for both cases.

                       regards, tom lane

... WHEN OTHERS THEN NULL

should be pretty safe to DROP a VIEW!

There's a somehow weird behavior.
This is what I create (sorry for lowecase):
--
create view timeref as select * from current_timestamp timeref;

create or replace function timeref()
returns void
language plpgsql
volatile
as $l0$
begin
drop view if exists pg_temp.timeref;
exception when others then null;
create or replace temporary view timeref as select
current_timestamp::timestamp with time zone timeref;
end
$l0$;

create or replace function timeref( t text )
returns void
language plpgsql
volatile
as $l0$
begin
execute format( 'create or replace temporary view timeref as select
%L::timestamp with time zone timeref',t );
end
$l0$;
--

Now the run(s):
--
tmp1=# SELECT * from timeref;
timeref
-------------------------------
2012-04-20 18:57:09.340628+02
(1 row)

Time: 0,250 ms
tmp1=# SELECT * from timeref();
timeref
---------

(1 row)

Time: 13,639 ms
tmp1=# SELECT * from timeref();
timeref
---------

(1 row)

Time: 40,494 ms
tmp1=# SELECT * from timeref();
NOTICE: view "timeref" does not exist, skipping
CONTEXT: SQL statement "drop view if exists pg_temp.timeref"
PL/pgSQL function "timeref" line 3 at SQL statement
timeref
---------

(1 row)

Time: 12,048 ms
--

As you can see, the third time I get a NOTICE message I don't get the
first two times.
Everything works fine but this strange thing...

2012/4/20 Vincenzo Romano <vincenzo.romano@notorand.it>:

2012/4/20 Vincenzo Romano <vincenzo.romano@notorand.it>:

2012/4/20 Tom Lane <tgl@sss.pgh.pa.us>:

Vincenzo Romano <vincenzo.romano@notorand.it> writes:

2012/4/20 Vincenzo Romano <vincenzo.romano@notorand.it>:

2012/4/20 Tom Lane <tgl@sss.pgh.pa.us>:

You might be able to use "DROP VIEW pg_temp.foo", which will either
drop a temp view of your own session or throw an error if there is none.

It works only if you have created at least one temporary object.
Until that the pg_temp "meta schema" doesn't resolve to a real schema
and the exception is thrown!

I didn't say *which* error would get thrown ;-).  If you are checking
that you'd need to be prepared for both cases.

                       regards, tom lane

... WHEN OTHERS THEN NULL

should be pretty safe to DROP a VIEW!

There's a somehow weird behavior.
This is what I create (sorry for lowecase):
--
create view timeref as select * from current_timestamp timeref;

create or replace function timeref()
returns void
language plpgsql
volatile
as $l0$
begin
 drop view if exists pg_temp.timeref;
 exception when others then null;
 create or replace temporary view timeref as select
current_timestamp::timestamp with time zone timeref;
end
$l0$;

create or replace function timeref( t text )
returns void
language plpgsql
volatile
as $l0$
begin
 execute format( 'create or replace temporary view timeref as select
%L::timestamp with time zone timeref',t );
end
$l0$;
--

Now the run(s):
--
tmp1=# SELECT * from timeref;
           timeref
-------------------------------
 2012-04-20 18:57:09.340628+02
(1 row)

Time: 0,250 ms
tmp1=# SELECT * from timeref();
 timeref
---------

(1 row)

Time: 13,639 ms
tmp1=# SELECT * from timeref();
 timeref
---------

(1 row)

Time: 40,494 ms
tmp1=# SELECT * from timeref();
NOTICE:  view "timeref" does not exist, skipping
CONTEXT:  SQL statement "drop view if exists pg_temp.timeref"
PL/pgSQL function "timeref" line 3 at SQL statement
 timeref
---------

(1 row)

Time: 12,048 ms
--

As you can see, the third time I get a NOTICE message I don't get the
first two times.
Everything works fine but this strange thing...

There's a typo (extra create temporary view), Sorry,

Vincenzo Romano <vincenzo.romano@notorand.it> writes:

As you can see, the third time I get a NOTICE message I don't get the
first two times.
Everything works fine but this strange thing...

There's a typo (extra create temporary view), Sorry,

Hm, yeah, the first time would throw an error because pg_temp doesn't
resolve, so if you're using DROP IF EXISTS there would be a visible
difference in behavior between the two cases.

I wonder if DROP IF EXISTS should consider non-existence of the
specified schema (if any) as being a "not exists" case, and not throw
an error for it. This isn't specific to the temp schema at all IMO.

regards, tom lane

2012/4/20 Tom Lane <tgl@sss.pgh.pa.us>:

Vincenzo Romano <vincenzo.romano@notorand.it> writes:

As you can see, the third time I get a NOTICE message I don't get the
first two times.
Everything works fine but this strange thing...

There's a typo (extra create temporary view), Sorry,

Hm, yeah, the first time would throw an error because pg_temp doesn't
resolve, so if you're using DROP IF EXISTS there would be a visible
difference in behavior between the two cases.

I wonder if DROP IF EXISTS should consider non-existence of the
specified schema (if any) as being a "not exists" case, and not throw
an error for it.  This isn't specific to the temp schema at all IMO.

                       regards, tom lane

The weirdness is that it doesn't produce any notice the first two times.
At the third invocation I see the notice coming out.
The test has been run on an empty database and a fresh new connection.
You can try the code yourself.
(I'm running 9.1.3 on Ubuntu Linux)

Vincenzo Romano <vincenzo.romano@notorand.it> writes:

The weirdness is that it doesn't produce any notice the first two times.
At the third invocation I see the notice coming out.

I'd suggest tweaking the exception handler to print the error it caught;
that would probably clarify what is happening.

regards, tom lane