Re: regclass and search_path

Hi Tom,

On 03/18/2011 12:17 AM, Tom Lane wrote:
> Joe Abbate<jma(at)freedomcircle(dot)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