Thread: Drop temporary table only if it exists
<p>Hi Everybody,<p>How do I drop a temporary table ( or for that matter a sequence) only if it exists or create them ifthey donot exist ? I want to do this to avoid any errors<p>Any help is appreciated .<p>Thanks,<p>Tarun<p><br /><hr size="1"/>Do you Yahoo!?<br /><a href="http://rd.yahoo.com/O=1/I=brandr/vday03/text/flow/*http://shopping.yahoo.com /shop?d=browse&id=20146735">Yahoo! Shopping</a> - Send Flowers for Valentine's Day
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
<p>Thanks <p> <b><i>Josh Berkus <josh@agliodbs.com></i></b> wrote: <blockquote style="PADDING-LEFT: 5px; MARGIN-LEFT:5px; BORDER-LEFT: #1010ff 2px solid">Mintoo,<br /><br />> How do I drop a temporary table ( or for that mattera sequence) only if it<br />> exists or create them if they donot exist ? I want to do this to avoid any<br />>errors<br /><br />A quick check for existance: <br /><br />SELECT * FROM pg_class WHERE relname = 'name_of_temp_table'and relkind = ?<br /><br />You'll want to look in the docs about the PG system tables to see what <br/>"relkind" you should test on for each type of object.<br /><br />-- <br />Josh Berkus<br />Aglio Database Solutions<br/>San Francisco</blockquote><p><br /><hr size="1" />Do you Yahoo!?<br /><a href="http://rd.yahoo.com/O=1/I=brandr/vday03/text/flow/*http://shopping.yahoo.com /shop?d=browse&id=20146735">Yahoo! Shopping</a> - Send Flowers for Valentine's Day
-----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_namespaceWHERE 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_namespaceWHERE 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-----
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 #
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 BerkusAglio Database SolutionsSan Francisco
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 #
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