Search Postgresql Archives

checking for temp tables information_schema vs. EXCEPTION

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux