Hi Tom,
On 03/18/2011 12:17 AM, Tom Lane wrote:
Joe Abbate<j...@freedomcircle.com> writes:
I'm using this to validate a tool I'm building and I get an error on the
following query:
autodoc=> SELECT conname::regclass FROM pg_constraint
autodoc-> WHERE contype = 'u';
ERROR: relation "product_product_code_key" does not exist
Ummm ... pg_constraint.conname contains a constraint name, not a table
name, so casting it to regclass is highly likely to fail. This hasn't
got anything to do with search_path AFAICS, it's just a thinko.
Depending on what it is that you're hoping to do, any of conrelid,
confrelid, or conindid might be what you're after. All of those columns
would contain pg_class OIDs that could usefully be cast to regclass.
Well, the pg_constraint.conname value exists as a relname in pg_class,
and the query works with constraints that don't cross schemas as
autodoc's does (or if you add all necessary schemas to your
search_path). For example,
moviesdb=> alter table film add unique (title);
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index
"film_title_key" for table "film"
ALTER TABLE
moviesdb=> SELECT conname::regclass FROM pg_constraint WHERE contype = 'u';
conname
----------------
film_title_key
(1 row)
For my immediate needs, the query was actually the target of a NOT IN
subquery of a query against pg_index (trying to exclude tuples of
indexes for UNIQUE constraints) and I've solved that by using conrelid
in the subquery (and indrelid in the main query). Nevertheless, I think
regclass should probably be smarter and work with anything in pg_class
(regardless of search_path).
Regards,
Joe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
