Thread: Temporary tables

<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 

On Saturday 27 September 2003 14:31, George A.J wrote:
> 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 "

This is because plpgsql is "compiled" and so the reference to tempTable gets 
fixed the first time it is called. In your case, the oid was 219389.

Now, the second time you call the function, the temp table gets re-created, 
gets a new OID and the old reference is no longer valid your insert line.

There are two solutions:
1. Use pltcl/plperl or some other interpreted language that doesn't compile in 
table references.
2. Build your insert statement using EXECUTE ''INSERT INTO tempTable ''...

This second passes the query string into the parser, so it works just fine for 
your example. I think some of this is covered in the manuals, you can 
certainly find plenty on it in the archives.

--  Richard Huxton Archonet Ltd

"George A.J" <jinujosein@yahoo.com> writes:
> 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?

Not directly, but you could try something like
perform * from pg_class where  relname = 'mytable' and pg_table_is_visible(oid);if not found then ...  -- create the
table

The visibility test would not succeed for temp tables belonging to other
backends.  (If 'mytable' is also used as the name of a regular table
then this isn't quite good enough, but I think just avoiding such a name
collision is easier than extending the check to reject non-temp tables.)
        regards, tom lane

Hi,

Try the below steps ..

1. Write one procedure to create tempory table (generic purpose)
**********************************************************************
CREATE OR REPLACE FUNCTION SP_CREATE_TEMP_TABLE () RETURNS VARCHAR AS '
DECLARE
L_SchemaName name;
BEGINEXECUTE ''CREATE TEMPORARY TABLE temp_table_gen (X VARCHAR);'';SELECT schemaname INTO L_SchemaName FROM
pg_stat_user_tableswhere relname 
 
=''temp_table_gen'';RETURN L_SchemaName;
END;
' LANGUAGE 'plpgsql';
**********************************************************************

2. Call the above (generic) procedure to get the temporary table schema 
name.. by using that schema name ..you can check whether the (real) 
temporary table is exists or not.

**************************************************************************
select into L_SchemaName * from SP_CREATE_TEMP_TABLE();  -- get the 
schemanameexecute ''drop table temp_table_gen;'';   -- drop the temptableselect schemaname into L_Schema from
pg_stat_user_tableswhere 
 
relname=''temp_total_count''  and schemaname =''''||L_SchemaName||'''';if (L_Schema is null) then    EXECUTE ''CREATE
TEMPORARYTABLE temp_total_count (TOTAL_COUNT 
 
NUMERIC);'';ELSE    EXECUTE ''DELETE FROM temp_total_count;'';END IF;
**************************************************************************

I hope this will help u to solve these temporary table issues..

With Regards
Vijay


>From: "George A.J" <jinujosein@yahoo.com>
>To: pgsql-sql@postgresql.org
>Subject: [SQL] Temporary tables
>Date: Sat, 27 Sep 2003 06:31:39 -0700 (PDT)
>
>
>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

_________________________________________________________________
Keep up with the pace of change. Register for My Tech Ed. 
http://server1.msn.co.in/sp03/teched/index.asp Realise your potential!