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