Обсуждение: How to drop a temporary view?
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. -- Vincenzo Romano - NotOrAnd.IT Information Technologies NON QVIETIS MARIBVS NAVTA PERITVS
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 > > -- > Vincenzo Romano - NotOrAnd.IT > Information Technologies > NON QVIETIS MARIBVS NAVTA PERITVS >
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