Multiple foreign keys with the same name and information_schema

[Jonathan Tapicer <tapicer@xxxxxxxxx>]

Hello everyone, I have a question regarding foreign keys and
information_schema. Given the following valid schema:

CREATE TABLE "Cat"
(
  "IdCat" serial NOT NULL,
  CONSTRAINT "PK_Cat" PRIMARY KEY ("IdCat")
);

CREATE TABLE "Art"
(
  "IdArt" serial NOT NULL,
  "IdCat" integer NOT NULL,
  CONSTRAINT "PK_Art" PRIMARY KEY ("IdArt"),
  CONSTRAINT "FK_Art_Cat" FOREIGN KEY ("IdCat")
      REFERENCES "Cat" ("IdCat") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE "Cat2"
(
  "IdCat2" serial NOT NULL,
  CONSTRAINT "PK_Cat2" PRIMARY KEY ("IdCat2")
);

CREATE TABLE "Art2"
(
  "IdArt2" serial NOT NULL,
  "IdCat2" integer NOT NULL,
  CONSTRAINT "PK_Art2" PRIMARY KEY ("IdArt2"),
  CONSTRAINT "FK_Art_Cat" FOREIGN KEY ("IdCat2")
      REFERENCES "Cat2" ("IdCat2") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

PostgreSQL, unlike other DBMSs, allows foreign keys on different
tables to have the same name (note FK_Art_Cat on "Art" and "Art2"). I
need to make a query to the information_schema catalog to get the
table referenced by a given field in a given table (eg: Art, IdCat
references Cat; Art2, IdCat2 references Cat2).

I was a able to do it using the pg_catalog tables, but I haven't found
a way to do it using information_schema since it relies on foreign
keys names being unique in the same catalog. Is this a known
limitation? Is there any way to do what I need with the
information_schema catalog? I want to make generic queries to use them
across different DBMSs that support the ANSI information_schema.

A possible solution would be adding the foreign key table_name to all
the tables on the information_schema that rely on foreign keys names
being unique, for the case I am talking about it would be enough to
have it the table referential_contraints.

Thanks,

Jonathan

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general