From: Marcin Krawczyk <jankes.mk@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: temp table existence
Date: Sat, 29 Dec 2007 21:43:07 +0100
Message-ID: <95f6bf9b0712291243h536b18e1ta5f7fd99f10fd793@mail.gmail.com> (raw)
In-Reply-To: <95f6bf9b0712291116lc3c1df0o978922176e98f8d7@mail.gmail.com>
References: <95f6bf9b0712291116lc3c1df0o978922176e98f8d7@mail.gmail.com>
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?
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jankes.mk@gmail.com
Subject: Re: temp table existence
In-Reply-To: <95f6bf9b0712291243h536b18e1ta5f7fd99f10fd793@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox