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

Question

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.

I think you need to query the information_schema.key_column_usage table. That schema isn't in your path, so start with something like select * from information_schema.key_column_usage;. — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Mar 25, 2013 at 15:33

Erwin Brandstetter · Accepted Answer · 2022-12-14 03:00:54Z

If you don't need this to be portable to another RDBMS (or another major Postgres version) it is 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 referenced_table, c.contype DESC;

Returns:

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

You only asked for the first column. I added two columns for context.

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 if necessary according to the current search_path. It is also double-quoted automatically where needed.

Check out details of pg_constraint and pg_attribute in the manual. More about object identifier types, too.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related