From: greg@turnstep.com
To: pgsql-sql@postgresql.org
Subject: Re: Drop temporary table only if it exists
Date: Fri, 14 Feb 2003 18:41:10 -0000
Message-ID: <784d32a2b3c0b617ad1f80c9d9c6f002@biglumber.com> (raw)
In-Reply-To: <200302140948.15889.josh@agliodbs.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-----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: greg@turnstep.com
Subject: Re: Drop temporary table only if it exists
In-Reply-To: <784d32a2b3c0b617ad1f80c9d9c6f002@biglumber.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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