Received: from nh.newgen.co.in (203-195-148-19.now-india.net.in [203.195.148.19] (may be forged)) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9U3ohP98075; Mon, 29 Oct 2001 22:50:46 -0500 (EST) (envelope-from dineshp@newgen.co.in) Received: from dinesh (ws240.newgen.co.in [192.168.5.240]) by nh.newgen.co.in (8.9.3/8.8.7) with SMTP id JAA21435; Tue, 30 Oct 2001 09:20:12 +0530 Message-ID: <000001c160f5$a0da2730$f005a8c0@dinesh> From: "Dinesh Parikh" To: , Subject: Check Existence of temporary table in a session. Date: Sat, 27 Oct 2001 17:07:06 +0530 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0011_01C15F09.CE2B67A0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200110/1119 X-Sequence-Number: 16918 This is a multi-part message in MIME format. ------=_NextPart_000_0011_01C15F09.CE2B67A0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi All, I have an strange problem . I am building an application with connection po= oling. 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 al= ready exists then do not create otherwise create it. The following query fa= ils. Select Into DBExistsFlag 1=20 Where Exists (Select * from Pg_Tables Where Upper(Trim(TableName)) =3D Uppe= r(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 =3D 1 Then Delete From FolderTree; End If;=20 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 ca= n i find that temporary table already exists so that irritating mesasage sh= ould not come. Any help/suggesion may be benificial for me. Thanks Regards Dinesh Parikh NSTL New Delhi ------=_NextPart_000_0011_01C15F09.CE2B67A0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi All,=
I have an strange problem . I am building = an=20 application with connection pooling. I had write a stored procedure and cal= led=20 from JDBC. In Procedure i am creating a table (temporary). I want to check = if=20 this temporary table already exists then do not create otherwise create it.= The=20 following query fails.
 
Select= Into=20 DBExistsFlag 1
Where = Exists=20 (Select * from Pg_Tables Where Upper(Trim(TableName)) =3D=20 Upper(Trim(''FolderTree'')));
 = If Not=20 Found Then
      Create Temp Table=20 FolderTree(FolderId Int8,ParentFolderId Int8,
          &= nbsp;=20         FolderTyp Char(1),Leaf Int4,RightFlag= =20 Char(1));
 End If;
 If DBExistsFlag =3D 1=20 Then
        Delete From=20 FolderTree;
 End If;
 
When I saw the table pgadmin_tables there&= nbsp;was=20 entry of table name pg_temp.232667.2 The columns of this table mached= with=20 my temporary table.
 
My question is that if postgres converts t= able name=20 to this one then how can i find that temporary table already exists so= that=20 irritating mesasage should not come.
 
Any help/suggesion may be benificial = for=20 me.
 
Thanks
 
Regards
Dinesh Parikh
NSTL New Delhi
 
 
 
------=_NextPart_000_0011_01C15F09.CE2B67A0--