Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id DB78C2E019D
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Sat, 29 Dec 2007 16:43:11 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 64695-02 for <pgsql-sql-postgresql.org@postgresql.org>;
	Sat, 29 Dec 2007 16:43:04 -0400 (AST)
Received: from an-out-0708.google.com (an-out-0708.google.com
 [209.85.132.251])
	by postgresql.org (Postfix) with ESMTP id 09AD42E00D4
	for <pgsql-sql@postgresql.org>; Sat, 29 Dec 2007 16:43:07 -0400 (AST)
Received: by an-out-0708.google.com with SMTP id c36so746814ana.33
	for <pgsql-sql@postgresql.org>; Sat, 29 Dec 2007 12:43:07 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references;
	bh=MrOOnoBBEuGzhk68KIeoyoQ5DZLHRgx/lFFOMW443Os=;
	b=q+lYyEsbdir2wtbXTkmbq53JKwvJhif3/fWcniOTWZIXthlfergYXodfYB1do5PZZOGJhc+W+z3zZ53X/7OMCogSMoTeZTK+EUqYokh6Plf7p1iR3pPkU2wEySdMm7F3O84CAVoJei3GSwP1sgMhQIf8elnWH1cLCQSHkd9Ul6I=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references;
	b=dzGgbhES7dJCR2xgsJ6MXGWRWJsVf1ZnqYtBrBsff2OqV3PtuE39m/XVKCtjxc+g37svxcur63DCVNMbVZVWQZyOY5BFf/yaeRvWPRpr/C2lXUCsjbccHZO2iY3Bo63qD8mr4hn9ye4i+u49X90oAnM8LE/0vCGL3r1g/e2xPW8=
Received: by 10.100.112.6 with SMTP id k6mr22236738anc.110.1198960987160;
	Sat, 29 Dec 2007 12:43:07 -0800 (PST)
Received: by 10.100.92.16 with HTTP; Sat, 29 Dec 2007 12:43:07 -0800 (PST)
Message-ID: <95f6bf9b0712291243h536b18e1ta5f7fd99f10fd793@mail.gmail.com>
Date: Sat, 29 Dec 2007 21:43:07 +0100
From: "Marcin Krawczyk" <jankes.mk@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: temp table existence
In-Reply-To: <95f6bf9b0712291116lc3c1df0o978922176e98f8d7@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_9002_20990630.1198960987157"
References: <95f6bf9b0712291116lc3c1df0o978922176e98f8d7@mail.gmail.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200712/154
X-Sequence-Number: 29846

------=_Part_9002_20990630.1198960987157
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Thanks for the answer but it's not quite sufficient. The code supplied on
his page:

CREATE OR REPLACE FUNCTION ...
BEGIN
PERFORM 1 FROM pg_catalog.pg_tables
WHERE tablename = 'xx' AND schemaname LIKE 'pg_temp%';
IF FOUND THEN
TRUNCATE xx;
ELSE
CREATE TEMP TABLE xx(...
END IF;

The function does exactly what I was trying to avoid - simple check the
existence of xx table in pg_tables virtualy only by it's name, it's not
enough since there may be other temp tables
created in seprate sessions. The only thing those temp table differ in
pg_tables i schemaname, they have
that
suffix number and in the above mentioned function I would have to be
able to retrieve this number somehow.

...
WHERE tablename = 'xx' AND schemaname LIKE 'pg_temp_' ||
function_to_retieve_suffix() ???
...

That would work. Otherwise all temp tables by the name of xx will be
truncated, which I would not like to happen since since they may still be in
use.


2007/12/29, Marcin Krawczyk <jankes.mk@gmail.com>:
>
> Hi all. Is there a way to determine the existence of a TEMP
> TABLE? I need to check i it exists before I create it. Doing simple check on pg_class or pg_tables is
> not enough because there may be other such tables created in other
> sessions. Or maybe anyone knows the identification (apart from 'others') of
> error to trap it with EXCEPTION clause?
>

------=_Part_9002_20990630.1198960987157
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Thanks for the answer but it&#39;s not quite sufficient. The code supplied on his page:<br><br>CREATE OR REPLACE FUNCTION ... <br>  BEGIN <br>    PERFORM 1 FROM pg_catalog.pg_tables <br>      WHERE tablename = &#39;xx&#39; AND schemaname LIKE &#39;pg_temp%&#39;; 
<br>    IF FOUND THEN <br>      TRUNCATE xx; <br>    ELSE <br>      CREATE TEMP TABLE xx(... <br>    END IF;<br><br>The function does exactly what I was trying to avoid - simple check the existence of xx table in pg_tables virtualy only by it&#39;s name, it&#39;s not enough since there may be other temp tables created&nbsp;in&nbsp;seprate&nbsp;sessions.&nbsp;The&nbsp;only&nbsp;thing&nbsp;those&nbsp;temp&nbsp;table&nbsp;differ&nbsp;in&nbsp;pg_tables&nbsp;i&nbsp;schemaname,&nbsp;they&nbsp;have&nbsp;
<br> that suffix&nbsp;number&nbsp;and&nbsp;in&nbsp;the&nbsp;above&nbsp;mentioned&nbsp;function&nbsp;I&nbsp;would&nbsp;have&nbsp;to&nbsp;be&nbsp;able&nbsp;to&nbsp;retrieve&nbsp;this&nbsp;number&nbsp;somehow.<br><br>...<br>WHERE tablename = &#39;xx&#39; AND schemaname LIKE &#39;pg_temp_&#39; || function_to_retieve_suffix() ???
<br>...<br><br> That would work. Otherwise all temp tables by the name of xx will be truncated, which I would not like to happen since since they may  still be in use.<br><br><br><div><span class="gmail_quote">2007/12/29, Marcin Krawczyk &lt;
<a href="mailto:jankes.mk@gmail.com">jankes.mk@gmail.com</a>&gt;:</span><blockquote class="gmail_quote" style="margin-top: 0; margin-right: 0; margin-bottom: 0; margin-left: 0; margin-left: 0.80ex; border-left-color: #cccccc; border-left-width: 1px; border-left-style: solid; padding-left: 1ex">
Hi all. Is there a way to determine the existence of a TEMP TABLE?&nbsp;I&nbsp;need&nbsp;to&nbsp;check&nbsp;i&nbsp;it&nbsp;exists&nbsp;before&nbsp;I&nbsp;create&nbsp;it.&nbsp;Doing&nbsp;simple&nbsp;check&nbsp;on&nbsp;pg_class&nbsp;or&nbsp;pg_tables&nbsp;is<br>not&nbsp;enough because there may be other such tables created in other sessions. Or maybe anyone knows the identification (apart from &#39;others&#39;) of error to trap it with EXCEPTION clause? 
<br></blockquote></div><br>

------=_Part_9002_20990630.1198960987157--