Re: How to drop a temporary view?

2012/4/20 Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it>:
> 2012/4/20 Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it>:
>> 2012/4/20 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>>> Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it> writes:
>>>> 2012/4/20 Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it>:
>>>>> 2012/4/20 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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,