pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedtemp table existence
8+ messages / 4 participants
[nested] [flat]
* temp table existence
@ 2007-12-29 19:16 Marcin Krawczyk <jankes.mk@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: Marcin Krawczyk @ 2007-12-29 19:16 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: temp table existence
@ 2007-12-29 19:33 Pavel Stehule <pavel.stehule@gmail.com>
parent: Marcin Krawczyk <jankes.mk@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Pavel Stehule @ 2007-12-29 19:33 UTC (permalink / raw)
To: Marcin Krawczyk <jankes.mk@gmail.com>; +Cc: pgsql-sql
Hello
On 29/12/2007, Marcin Krawczyk <jankes.mk@gmail.com> wrote:
> 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?
>
http://www.pgsql.cz/index.php/Automatic_execution_plan_caching_in_PL/pgSQL
Regards
Pavel Stehule
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: temp table existence
@ 2007-12-29 20:43 Marcin Krawczyk <jankes.mk@gmail.com>
parent: Marcin Krawczyk <jankes.mk@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Marcin Krawczyk @ 2007-12-29 20:43 UTC (permalink / raw)
To: pgsql-sql
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?
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: temp table existence
@ 2007-12-29 21:16 Marcin Krawczyk <jankes.mk@gmail.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: Marcin Krawczyk @ 2007-12-29 21:16 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-sql
I just realized something... my bad. It will work since TRUNCATE removes
only table from current session.
Thank you again.
Regards
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: temp table existence
@ 2007-12-29 21:23 Pavel Stehule <pavel.stehule@gmail.com>
parent: Marcin Krawczyk <jankes.mk@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Pavel Stehule @ 2007-12-29 21:23 UTC (permalink / raw)
To: Marcin Krawczyk <jankes.mk@gmail.com>; +Cc: pgsql-sql
Hello
my solution isn't 100% perfect too. Better is test visibility:
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as
"Type",
r.rolname as "Owner"
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname <> 'pg_catalog' -- replace LIKE 'pg_temp%';
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
for existence test this query can be simplified
Regards
Pavel Stehule
On 29/12/2007, Marcin Krawczyk <jankes.mk@gmail.com> wrote:
> I just realized something... my bad. It will work since TRUNCATE removes
> only table from current session.
>
> Thank you again.
> Regards
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: temp table existence
@ 2007-12-29 22:25 Adrian Klaver <aklaver@comcast.net>
0 siblings, 0 replies; 8+ messages in thread
From: aklaver@comcast.net @ 2007-12-29 22:25 UTC (permalink / raw)
To: Marcin Krawczyk <jankes.mk@gmail.com>; pgsql-sql
------------- Original message ----------------------
From: "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?
select * from temp_table limit 1;
Trap error 42P01 Undefined table.
--
Adrian Klaver
aklaver@comcast.net
-
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: temp table existence
@ 2007-12-30 06:26 Erik Jones <erik@myemma.com>
parent: Marcin Krawczyk <jankes.mk@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Erik Jones @ 2007-12-30 06:26 UTC (permalink / raw)
To: Marcin Krawczyk <jankes.mk@gmail.com>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; pgsql-sql
On Dec 29, 2007, at 3:16 PM, Marcin Krawczyk wrote:
> I just realized something... my bad. It will work since TRUNCATE
> removes only table from current session.
If the table exists and you're going to TRUNCATE it before using it,
you could just use DROP TABLE IF EXISTS and then create it. I don't
know, that feels cleaner to me than TRUNCATEing a table that might
not be available to the session.
Erik Jones
Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: temp table existence
@ 2007-12-30 08:44 Pavel Stehule <pavel.stehule@gmail.com>
parent: Erik Jones <erik@myemma.com>
0 siblings, 0 replies; 8+ messages in thread
From: Pavel Stehule @ 2007-12-30 08:44 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: Marcin Krawczyk <jankes.mk@gmail.com>; pgsql-sql
Hello
On 30/12/2007, Erik Jones <erik@myemma.com> wrote:
>
> On Dec 29, 2007, at 3:16 PM, Marcin Krawczyk wrote:
>
> > I just realized something... my bad. It will work since TRUNCATE
> > removes only table from current session.
>
> If the table exists and you're going to TRUNCATE it before using it,
> you could just use DROP TABLE IF EXISTS and then create it. I don't
> know, that feels cleaner to me than TRUNCATEing a table that might
> not be available to the session.
>
if you drop temp table in session, you lost all prepared statements
related to table. before 8.3 it means runtime error, now only less
efectivity.
> Erik Jones
>
> Software Developer | Emma(R)
> erik@myemma.com
> 800.595.4401 or 615.292.5888
> 615.292.0777 (fax)
>
> Emma helps organizations everywhere communicate & market in style.
> Visit us online at http://www.myemma.com
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-12-30 08:44 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-12-29 19:16 temp table existence Marcin Krawczyk <jankes.mk@gmail.com>
2007-12-29 19:33 ` Pavel Stehule <pavel.stehule@gmail.com>
2007-12-29 21:16 ` Marcin Krawczyk <jankes.mk@gmail.com>
2007-12-29 21:23 ` Pavel Stehule <pavel.stehule@gmail.com>
2007-12-30 06:26 ` Erik Jones <erik@myemma.com>
2007-12-30 08:44 ` Pavel Stehule <pavel.stehule@gmail.com>
2007-12-29 20:43 ` Marcin Krawczyk <jankes.mk@gmail.com>
2007-12-29 22:25 Re: temp table existence Adrian Klaver <aklaver@comcast.net>
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