Temporary tables - Mailing list pgsql-sql

<p>hi,<p>I am using postgresql 7.3.2. Is there any function to determine <br />whether a table exists in the
database.Oris there any function <br />that returns the current temp schema.<br />I am using a pl/pgsql function that
createand drop a temporary table.<br />The procedure run correctly for the first time for each database connection. <br
/>IfI run the same procedure second time in the same connection it produces the error<p>"ERROR:  pg_class_aclcheck:
relation219389 not found<br />WARNING:  Error occurred while executing PL/pgSQL function testFun<br />WARNING:  line 20
atSQL statement "<p>Here is the function ....<p>---------------------------------------------------------<br />CREATE
ORREPLACE FUNCTION testFun( varchar(10) ) RETURNS setof int<br />AS<br />'<br />DECLARE<br />     --Aliases for
parameters<br/>     vSBAcNo ALIAS FOR $1;<br />     --local variables<br />     vRow RECORD;<br />     <br />BEGIN<br
/>    -- create a tempory table to hold the numbers<br />     CREATE TABLE tempTable<br />     (<br />          testNo
int<br/>     ) ;<br />     <br />    for vRow IN select Entryno from  EntryTable LOOP<br />        <br /> return next
vRow.Entryno;<br/>       <br />        insert into tempTable values( vRow.Entryno);<br />                       <br
/>   end loop;<p>    drop table tempTable;<p>    return;<br />     <br />END;'<p>LANGUAGE
'plpgsql';<p>-------------------------------------------------------------<p>Ifi commented the "insert into tempTable
values(vRow.Entryno);" line<br />the function works correctly. The problem is the oid of tempTable is kept when <br
/>thefunction is first executed. the next execution creates another table with <br />different oid. So the insert
fails.<p>I want to check whether the temporary table exist. If exist do not create the <br />temporary table in
subsequentcalls and do not dorp it. This will solve the problem.<p>When i searched the pg_class i found the temp table
namemore than once. <br />ie, a temporary table is created for each connection.I cannot distingush <br />the temp
tables.But the tables are in different schema.<br />Is there a method to get the current temporary schema? How postgres
distinguish<br/>this temp tables?.Is there a way to distinguish temporary tables.<br />The entries in pg_class table is
sameexcept the schema.<br />When i used the current_schema() function it returns public.<p>There is a lot of functions
thatuses temporary tables. I think that there is <br />an option when creating temp tables in postgres 7.4 . But no way
touse 7.4 <br />now it is a working database.<p>can i write a function to check the existance of the temporary
table...<br/>please help...<p>jinujose<p><hr size="1" /> Do you Yahoo!?<br /><a
href="http://shopping.yahoo.com/?__yltc=s%3A150000443%2Cd%3A22708228%2Cslk%3Atext%2Csec%3Amail">TheNew Yahoo!
Shopping</a>- with improved product search