Re: [GENERAL] regclass and search_path
HI Tom, On 03/18/2011 12:42 AM, Joe Abbate wrote: 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). On second thought, conname is just a "name", is not unique and is lacking schema/namespace info. As you said, a thinko. 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
Re: [GENERAL] regclass and search_path
Hi Tom, On 03/18/2011 12:17 AM, Tom Lane wrote: Joe Abbate 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
Re: [GENERAL] regclass and search_path
Joe Abbate 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. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] regclass and search_path
Hi, I'm using the autodoc regression database available at http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/autodoc/autodoc/regressdatabase.sql?rev=1.2&content-type=text/x-cvsweb-markup This has several schemas that have cross-schema foreign key constraints such as the following: autodoc=> \d product.product Table "product.product" Column| Type | Modifiers -+-+-- product_id | integer | not null default nextval('product.product_product_id_seq'::regclass) product_code| text| not null product_description | text| Indexes: "product_pkey" PRIMARY KEY, btree (product_id) "product_product_code_key" UNIQUE, btree (product_code) Check constraints: "product_product_code_check" CHECK (product_code = upper(product_code)) Referenced by: TABLE "store.inventory" CONSTRAINT "inventory_product_id_fkey" FOREIGN KEY (product_id) REFERENCES product.product(product_id) ON UPDATE CASCADE ON DELETE RESTRICT TABLE "warehouse.inventory" CONSTRAINT "inventory_product_id_fkey" FOREIGN KEY (product_id) REFERENCES product.product(product_id) ON UPDATE CASCADE ON DELETE RESTRICT 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 The 8.4 documentation says: The regclass input converter handles the table lookup according to the schema path setting, and so it does the "right thing" automatically. My search path is the default "$user", public and I'm only able to avoid the error if I set the search_path to cover all the schemas, e.g., autodoc=> set search_path to "$user", public, product, store, warehouse; SET autodoc=> SELECT conname::regclass FROM pg_constraint WHERE contype = 'u'; conname product_product_code_key store_store_code_key warehouse_warehouse_code_key warehouse_warehouse_supervisor_key (4 rows) I would've thought that the "right thing" would have involved prepending the schema to the constraint name, e.g., product.product_product_code_key as is done for the table names in the \d output. Is this a bug or does regclass only do the "right thing" for tables and not for constraints? Joe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general