[GENERAL] Check Existence of temporary table in a session.
Dinesh Parikh
------=_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
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-1" http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.3105.105" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi </FONT><FONT face=3DArial size=3D2>All,=
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>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.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT color=3D#c0c0c0 face=3D"Courier New" size=3D2><EM><STRONG>Select=
Into=20
DBExistsFlag 1 </STRONG></EM></FONT></DIV>
<DIV><FONT color=3D#c0c0c0 face=3D"Courier New" size=3D2><EM><STRONG>Where =
Exists=20
(Select * from Pg_Tables Where Upper(Trim(TableName)) =3D=20
Upper(Trim(''FolderTree'')));</STRONG></EM></FONT></DIV>
<DIV><STRONG><EM><FONT color=3D#c0c0c0 face=3D"Courier New" size=3D2> =
If Not=20
Found Then<BR> Create Temp Table=20
FolderTree(FolderId Int8,ParentFolderId Int8,</FONT></EM></STRONG></DIV>
<DIV><EM><FONT face=3D"Courier New" size=3D2><FONT=20
color=3D#c0c0c0><STRONG> &=
nbsp;=20
FolderTyp Char(1),Leaf Int4,RightFlag=
=20
Char(1));<BR> End If;<BR> If DBExistsFlag =3D 1=20
Then<BR> Delete From=20
FolderTree;<BR> End If; <BR></STRONG></FONT></DIV></FONT></EM>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>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.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>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.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>Any help/suggesion may be benificial =
for=20
me.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>Thanks</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>Regards</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Dinesh Parikh</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>NSTL New Delhi</FONT></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV></BODY></HTML>
------=_NextPart_000_0011_01C15F09.CE2B67A0--