Arun Arun - 2 months ago 12
SQL Question

Find the referenced table name using table, field and schema name

I have a requirement where I need to find the referenced table name (Primary key table name) by a particular field in a table (Foreign key table) using this field name, table name (where this field resides) and the schema name (where the table and thereby the field resides)

For example:

Schema1.TableA
Id (Integer, PK)
Name varchar


Schema2.TableB
Id (integer, PK)
A_Id (integer, FK referencing TableA.Id)
Name varchar


I need to pass
A_Id
,
TableB
and
Schema2
to a function and get
Schema1.TableA
as result.

I am using Postgres 8.3.

Answer

If you don't need this to be portable to another RDBMS it is much faster and simpler to use the catalog tables in pg_catalog instead of the standard information schema:

SELECT c.confrelid::regclass::text AS referenced_table
     , c.conname AS fk_name
     , pg_get_constraintdef(c.oid) AS fk_definition
FROM   pg_attribute a 
JOIN   pg_constraint c ON (c.conrelid, c.conkey[1]) = (a.attrelid, a.attnum)
WHERE  a.attrelid = '"Schema2"."TableB"'::regclass   -- table name
AND    a.attname  = 'A_Id'                           -- column name  
AND    c.contype  = 'f'
ORDER  BY conrelid::regclass::text, contype DESC;

Returns:

 referenced_table | fk_name  |  fk_definition
------------------+-------------------------+----------------------------------------------
 Schema1.TableA   | b1_fkey  | FOREIGN KEY ("B_id") REFERENCES "Schema1"."TableA"("A_id")

Notes

  • The additional two columns are for orientation only. According to your Q, you only need the first column.

  • This returns all referenced tables by all foreign keys involving the given column name - including FK constraints on multiple columns.

  • The name is automatically schema-qualified or not according to the visibility set by the current search_path. The name is also escaped where needed (illegal or upper case characters, reserved words, ...) automatically, too.

Check out details of pg_constraint and pg_attribute in the manual. And more about object identifier types as well.

Related: