pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDrop temporary table only if it exists
8+ messages / 4 participants
[nested] [flat]
* Drop temporary table only if it exists
@ 2003-02-14 15:34 Mintoo Lall <tlqmail@yahoo.com>
0 siblings, 2 replies; 8+ messages in thread
From: Mintoo Lall @ 2003-02-14 15:34 UTC (permalink / raw)
To: pgsql-sql
Hi Everybody,
How do I drop a temporary table ( or for that matter a sequence) only if it exists or create them if they donot exist ? I want to do this to avoid any errors
Any help is appreciated .
Thanks,
Tarun
---------------------------------
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Drop temporary table only if it exists
@ 2003-02-14 17:48 Josh Berkus <josh@agliodbs.com>
parent: Mintoo Lall <tlqmail@yahoo.com>
1 sibling, 2 replies; 8+ messages in thread
From: Josh Berkus @ 2003-02-14 17:48 UTC (permalink / raw)
To: Mintoo Lall <tlqmail@yahoo.com>; pgsql-sql
Mintoo,
> How do I drop a temporary table ( or for that matter a sequence) only if it
> exists or create them if they donot exist ? I want to do this to avoid any
> errors
A quick check for existance:
SELECT * FROM pg_class WHERE relname = 'name_of_temp_table' and relkind = ?
You'll want to look in the docs about the PG system tables to see what
"relkind" you should test on for each type of object.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Drop temporary table only if it exists
@ 2003-02-14 18:12 Mintoo Lall <tlqmail@yahoo.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 8+ messages in thread
From: Mintoo Lall @ 2003-02-14 18:12 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; pgsql-sql
Thanks
Josh Berkus <josh@agliodbs.com> wrote:Mintoo,
> How do I drop a temporary table ( or for that matter a sequence) only if it
> exists or create them if they donot exist ? I want to do this to avoid any
> errors
A quick check for existance:
SELECT * FROM pg_class WHERE relname = 'name_of_temp_table' and relkind = ?
You'll want to look in the docs about the PG system tables to see what
"relkind" you should test on for each type of object.
--
Josh Berkus
Aglio Database Solutions
San Francisco
---------------------------------
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Drop temporary table only if it exists
@ 2003-02-14 18:41 greg@turnstep.com
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 8+ messages in thread
From: greg@turnstep.com @ 2003-02-14 18:41 UTC (permalink / raw)
To: pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> A quick check for existance:
>
> SELECT * FROM pg_class WHERE relname = 'name_of_temp_table' and relkind = ?
>
> You'll want to look in the docs about the PG system tables to see what
> "relkind" you should test on for each type of object.
If you are using 7.3, you also need to to consider the namespace:
temporary relations live in their own schema.
To find out if the temporary table (relkind='r') named "neptune" exists:
SELECT COUNT(*) FROM pg_class WHERE LOWER(relname) = 'neptune'
AND relkind = 'r'
AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname ~ '^pg_temp');
To find out about a sequence (relkind='S') named "saturn" exists:
SELECT COUNT(*) FROM pg_class WHERE LOWER(relname) = 'saturn'
AND relkind = 'S'
AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname ~ '^pg_temp');
To find out if "jupiter" is used by any temp relations:
SELECT COUNT(*) FROM pg_class WHERE LOWER(relname) = 'jupiter'
AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname ~ '^pg_temp');
To use the above on a pre-7.3 system, just remove the last line that
mentions relnamespace.
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200302141327
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html
iD8DBQE+TTdmvJuQZxSWSsgRAtVNAKDtWkTxxg++vqJ7asqMG54BDCzPiQCg3dBG
SbKhIdttHz4fVV1SMeyGqXE=
=+JMB
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Drop temporary table only if it exists
@ 2003-02-18 17:38 Jan Wieck <JanWieck@Yahoo.com>
parent: Mintoo Lall <tlqmail@yahoo.com>
1 sibling, 1 reply; 8+ messages in thread
From: Jan Wieck @ 2003-02-18 17:38 UTC (permalink / raw)
To: Mintoo Lall <tlqmail@yahoo.com>; +Cc: pgsql-sql
Mintoo Lall wrote:
>
> Hi Everybody,
>
> How do I drop a temporary table ( or for that matter a sequence) only
> if it exists or create them if they donot exist ? I want to do this
> to avoid any errors
I wonder what type of application cannot remember if it created a temp
object or not during the lifetime of it's database connection.
Jan
>
> Any help is appreciated .
>
> Thanks,
>
> Tarun
>
> ----------------------------------------------------------------------
> Do you Yahoo!?
> Yahoo! Shopping - Send Flowers for Valentine's Day
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Drop temporary table only if it exists
@ 2003-02-18 18:28 Josh Berkus <josh@agliodbs.com>
parent: Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 1 reply; 8+ messages in thread
From: Josh Berkus @ 2003-02-18 18:28 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; Mintoo Lall <tlqmail@yahoo.com>; +Cc: pgsql-sql
Jan,
> > How do I drop a temporary table ( or for that matter a sequence) only
> > if it exists or create them if they donot exist ? I want to do this
> > to avoid any errors
>
> I wonder what type of application cannot remember if it created a temp
> object or not during the lifetime of it's database connection.
A real one, Jan.
This sort of test is particularly necessary for Exception handling routines.
One wants the exception handler to drop all temporary obejcts for a clean
slate, but since an exception has occurred, one cannot be sure which objects
were created successfully.
I've personally written several such routines.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Drop temporary table only if it exists
@ 2003-02-18 20:47 Jan Wieck <JanWieck@Yahoo.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 8+ messages in thread
From: Jan Wieck @ 2003-02-18 20:47 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Mintoo Lall <tlqmail@yahoo.com>; pgsql-sql
Josh Berkus wrote:
>
> Jan,
>
> > > How do I drop a temporary table ( or for that matter a sequence) only
> > > if it exists or create them if they donot exist ? I want to do this
> > > to avoid any errors
> >
> > I wonder what type of application cannot remember if it created a temp
> > object or not during the lifetime of it's database connection.
>
> A real one, Jan.
>
> This sort of test is particularly necessary for Exception handling routines.
> One wants the exception handler to drop all temporary obejcts for a clean
> slate, but since an exception has occurred, one cannot be sure which objects
> were created successfully.
All temporary objects that need to persist across transactions should be
created at session start. Everything else is created inside the
transaction that needs it and is therefore automatically cleaned up.
If there are gazillions of those session level temporary objects, I
doubt that the database is really the right place to "cache" this sort
of data.
>
> I've personally written several such routines.
I never needed to. I might when I come around and create the next
generation of PL that supports packages with package initialization
functions and the like. Let's see ...
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Drop temporary table only if it exists
@ 2003-02-19 06:29 Josh Berkus <josh@agliodbs.com>
parent: Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 0 replies; 8+ messages in thread
From: Josh Berkus @ 2003-02-19 06:29 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: Mintoo Lall <tlqmail@yahoo.com>; pgsql-sql
Jan,
> All temporary objects that need to persist across transactions should be
> created at session start. Everything else is created inside the
> transaction that needs it and is therefore automatically cleaned up.
That's a good idea for Mintoo: open your temporary objects inside a
transaction. Then you can dismiss them by rolling back the transaction.
> I never needed to. I might when I come around and create the next
> generation of PL that supports packages with package initialization
> functions and the like. Let's see ...
Hey, drop a line by David Fetter. He's been making noises about overhauling
PL/pgSQL.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-02-19 06:29 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-14 15:34 Drop temporary table only if it exists Mintoo Lall <tlqmail@yahoo.com>
2003-02-14 17:48 ` Josh Berkus <josh@agliodbs.com>
2003-02-14 18:12 ` Mintoo Lall <tlqmail@yahoo.com>
2003-02-14 18:41 ` greg@turnstep.com
2003-02-18 17:38 ` Jan Wieck <JanWieck@Yahoo.com>
2003-02-18 18:28 ` Josh Berkus <josh@agliodbs.com>
2003-02-18 20:47 ` Jan Wieck <JanWieck@Yahoo.com>
2003-02-19 06:29 ` Josh Berkus <josh@agliodbs.com>
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