I didn't find a definitive answer on how to check for the existence
of a temporary table.
I did a not scientific test to see if I could see a temp table from
another connection in the information_schema... and I can't.
The schema system is more direct (so cleaner) but it seems to rely on
some "behind the scene trick" I don't fully understand.
I could do a list of insane things like:
begin;
create or replace function tt_test() returns void as
$$
declare
sch varchar(128);
begin
create temp table pippo (i int);
select into sch table_schema from information_schema.tables
where
table_name='pippo' and
table_type='LOCAL TEMPORARY';
execute 'create table ' || sch || '.pippo (i int);'; -- FAIL
execute 'create schema ' || sch || ';'; -- NOT TESTED
create table pippo (i int); -- SUCCEDE
create temp table zzz as
select * from information_schema.tables
where
table_name='pippo';
return;
end;
$$ language plpgsql;
select * from tt_test();
commit;
select * from zzz limit 10;
It looks like an invisible search path is added.
How temp schema name are obtained? Is there any place in the manual
that say that pg_temp_.* is a "reserved schema pattern"?
I didn't test but the EXCEPTION method may miss the difference
between the temp table and the permanent table. And schema
qualifying the temp table requires some further extra step.
So EXCEPTION method doesn't look safe.
Does EXCEPTION have some other hidden cost? Just for curiosity.
thanks
--
Ivan Sergio Borgonovo
http://www.webthatworks.it