Обсуждение: Which schema I am woking on??

Hi all,
  I am writing a pl/psql function with a temp table right now.
It creates the temp table inside. Whenever I call this function in a
session for the first time, it creates a shema, namely pg_temp_number,
automatically and it creates the temp table within the schema. I've
tried to add a check inside the function to check have the temp table
been already existed. The function will look like :

     CREATE OR REPLACE FUNCTION reffunc(refcursor, varchar(10),
varchar(10)) RETURNS refcursor AS '
     BEGIN
      IF (SELECT 1 WHERE EXISTS (SELECT * FROM pg_tables WHERE tablename

= ''tablexxx'')) = 1 THEN
        EXECUTE ''DROP TABLE tablexxx '';
      END IF;
        EXECUTE ''create local temp table tablexxx (repno character(15),

date date)'';
        EXECUTE ''insert into tablexxx (repno, date) VALUES (
''||quote_literal($2)||'', now() )'';
        EXECUTE ''insert into tablexxx (repno, date) VALUES (
''||quote_literal($3)||'', now() )'';
        OPEN $1 for EXECUTE '' SELECT * FROM tablexxx '';
      RETURN $1;
     END;
     ' LANGUAGE 'plpgsql';

It works fine as long as there is only one session (or only one client)
calling and called this function. For example, if one session has called

the funciton and the session is not terminated yet, then other session
call this function again, the function would find there is already a
table called tablexxx in the pg_tables and it tries to drop the table.
However, there is not table tablexxx belonged to the second session
actually. Therefore, the function would fail with this error message:

    ERROR:  table "tablexxx" does not exist

Firstly, is there anyway to find out which Schema I am working on, so I
could query the pg_tables with a specified schemaname??
I have tried to select current_schema. However, it always return
"public" to me.

Second, I am wondering how could I drop the automically created schema
automically? Could I config the system to make it drop the correpsonding

schema whenever a session is terminated?

Thank You Very Much.

Harry Yau

On Fri, Jul 11, 2003 at 10:45:57AM +0800, Harry Yau wrote:

> Firstly, is there anyway to find out which Schema I am working on, so I
> could query the pg_tables with a specified schemaname??
> I have tried to select current_schema. However, it always return
> "public" to me.

Actually, the temp schema is always empty for a new session.  I think
you can use something like this to detect if a table exists in the
current temp schema:

select * from pg_class
where relname='foo' and
relnamespace=(
        select oid
        from pg_namespace
        where nspname = (
                select 'pg_temp_'|| foo
                from pg_stat_get_backend_idset() as foo
                where pg_stat_get_backend_pid(foo)=pg_backend_pid()
                )
        );

> Second, I am wondering how could I drop the automically created schema
> automically? Could I config the system to make it drop the correpsonding
> schema whenever a session is terminated?

You don't need to, it already does that.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Si quieres ser creativo, aprende el arte de perder el tiempo"