Re: Drop temporary table only if it exists

-----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(at)turnstep(dot)com
PGP Key: 0x14964AC8 200302141327

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+TTdmvJuQZxSWSsgRAtVNAKDtWkTxxg++vqJ7asqMG54BDCzPiQCg3dBG
SbKhIdttHz4fVV1SMeyGqXE=
=+JMB
-----END PGP SIGNATURE-----