470,614 Members | 1,425 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,614 developers. It's quick & easy.

Which Schema I am working 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 '
= ''tablexxx'')) = 1 THEN
EXECUTE ''DROP TABLE tablexxx '';
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 '';
' 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??

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?


Nov 11 '05 #1
0 1059

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by stiank81 | last post: by
reply views Thread by Harry Yau | last post: by
8 posts views Thread by Poonam | last post: by
4 posts views Thread by Iain A. Mcleod | last post: by
reply views Thread by Derek | last post: by
5 posts views Thread by Reuven Nisser | last post: by
4 posts views Thread by cmay | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.