Temporary tables

hi,

I am using postgresql 7.3.2. Is there any function to determine
whether a table exists in the database.Or is there any function
that returns the current temp schema.
I am using a pl/pgsql function that create and drop a temporary table.
The procedure run correctly for the first time for each database connection.
If I run the same procedure second time in the same connection it produces the error

"ERROR: pg_class_aclcheck: relation 219389 not found
WARNING: Error occurred while executing PL/pgSQL function testFun
WARNING: line 20 at SQL statement "

Here is the function ....

---------------------------------------------------------
CREATE OR REPLACE FUNCTION testFun( varchar(10) ) RETURNS setof int
AS
'
DECLARE
--Aliases for parameters
vSBAcNo ALIAS FOR $1;
--local variables
vRow RECORD;

BEGIN
-- create a tempory table to hold the numbers
CREATE TABLE tempTable
(
testNo int
) ;

for vRow IN select Entryno from EntryTable LOOP

return next vRow.Entryno;

insert into tempTable values( vRow.Entryno);

end loop;

drop table tempTable;

return;

END;'

LANGUAGE 'plpgsql';

-------------------------------------------------------------

If i commented the "insert into tempTable values( vRow.Entryno);" line
the function works correctly. The problem is the oid of tempTable is kept when
the function is first executed. the next execution creates another table with
different oid. So the insert fails.

I want to check whether the temporary table exist. If exist do not create the
temporary table in subsequent calls and do not dorp it. This will solve the problem.

When i searched the pg_class i found the temp table name more than once.
ie, a temporary table is created for each connection.I cannot distingush
the temp tables. But the tables are in different schema.
Is there a method to get the current temporary schema? How postgres distinguish
this temp tables?.Is there a way to distinguish temporary tables.
The entries in pg_class table is same except the schema.
When i used the current_schema() function it returns public.

There is a lot of functions that uses temporary tables. I think that there is
an option when creating temp tables in postgres 7.4 . But no way to use 7.4
now it is a working database.

can i write a function to check the existance of the temporary table...
please help...

jinujose

---------------------------------
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search