From: greg@turnstep.com
To: pgsql-sql@postgresql.org
Subject: Re: Drop temporary table only if it exists
In-Reply-To: <200302140948.15889.josh@agliodbs.com>
Date: Fri, 14 Feb 2003 18:41:10 -0000
Message-ID: <784d32a2b3c0b617ad1f80c9d9c6f002@biglumber.com>


-----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-----