Geek Answers Handbook

Find the name of the link table using the name of the table, field, and schema

I have a requirement when I need to find the link table name (primary key table name) by a specific field in the table (foreign key table) using this field name, table name (where this field is located) and name scheme (where the table is and, thereby the field)

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 the result of Schema1.TableA .

I am using Postgres 8.3.

+1
sql postgresql constraints foreign-keys
source share
1 answer

If you do not need this to be portable to another RDBMS, it is much faster and easier to use the directory tables in pg_catalog instead of the standard information scheme:

 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;

Return:

  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 purposes only. According to your Q, you only need the first column.

  • This returns all referenced tables with all foreign keys containing the specified column name, including FK restrictions for multiple columns.

  • The name is automatically assigned to the schema or does not match the visibility set by the current search_path . This name is also automatically escaped if necessary (illegal or uppercase, reserved words, ...).

Check out the pg_constraint and pg_attribute in the manual. And more about the types of object identifiers .

on this topic:

+1
source share

More articles:


All Articles