Skip site navigation (1) Skip section navigation (2)

Check Existence of temporary table in a session.

From: "Dinesh Parikh" <dineshp(at)newgen(dot)co(dot)in>
To: <pgsql-general(at)postgresql(dot)org>, <pgsql-sql(at)postgresql(dot)org>
Subject: Check Existence of temporary table in a session.
Date: 2001-10-27 11:37:06
Message-ID: 000001c160f5$a0da2730$f005a8c0@dinesh (view raw, whole thread or download thread mbox)
Lists: pgsql-generalpgsql-sql
Hi All,
I have an strange problem . I am building an application with connection pooling. I had write a stored procedure and called from JDBC. In Procedure i am creating a table (temporary). I want to check if this temporary table already exists then do not create otherwise create it. The following query fails.

Select Into DBExistsFlag 1 
Where Exists (Select * from Pg_Tables Where Upper(Trim(TableName)) = Upper(Trim(''FolderTree'')));
 If Not Found Then
      Create Temp Table FolderTree(FolderId Int8,ParentFolderId Int8,
                    FolderTyp Char(1),Leaf Int4,RightFlag Char(1));
 End If;
 If DBExistsFlag = 1 Then
        Delete From FolderTree;
 End If; 

When I saw the table pgadmin_tables there was entry of table name  pg_temp.232667.2 The columns of this table mached with my temporary table.

My question is that if postgres converts table name to this one then how can i find that temporary table already exists so that irritating mesasage should not come.

Any help/suggesion may be benificial for me.


Dinesh Parikh
NSTL New Delhi

pgsql-sql by date

Next:From: Valdir H. Dias LeiteDate: 2001-10-27 16:38:05
Subject: SQL99
Previous:From: Johnny PredictoDate: 2001-10-27 07:29:27
Subject: PostgreSQL, Java & JDBC

pgsql-general by date

Next:From: postgresqlDate: 2001-10-27 15:45:40
Subject: Re: Sequential sequence numbers
Previous:From: Lincoln YeohDate: 2001-10-27 11:01:33
Subject: Re: Disable Transaction - plans ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group