使用表，字段和模式名称查找引用的表名 [英] Find the referenced table name using table, field and schema name

问题描述

我有一个需求，我需要通过使用此字段名称，表名（此字段所在的位置）和架构（表的外键表）中的特定字段来查找引用的表名（主键表名）名称（表格和字段所在的位置）

例如：

  Schema1.TableA 
 Id（Integer，PK）
 Name varchar 
 
 
 Schema2.TableB 
 Id（integer，PK）
 A_Id（整型，FK引用TableA.Id）
名称varchar 
  

我需要通过 A_Id ， TableB 和 Schema2 code> Schema1.TableA 作为结果。

我正在使用Postgres 8.3。

pg_catalog 而不是标准的信息模式：

  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  - 表名
 AND a.attname ='A_Id' - 列名
 AND c.contype ='f'
 ORDER BY conrelid :: regclass :: text，contype DESC; 
  

返回：

  referenced_table | fk_name | fk_definition 
 ------------------ + ------------------------- + ---------------------------------------------- 
 Schema1.TableA | b1_fkey | FOREIGN KEY（B_ID）REFERENCESSchema1。TableA（A_id）
  

• 其他两列仅供定位。根据你的Q，你只需要第一列。
  

• 这将通过所有涉及给定列的外键返回所有引用的表名称 - 在多列上包含FK约束。
  

• 该名称根据当前 search_path 。

<必填字段> p>查看 pg_constraint 和 pg_attribute 。还有更多关于对象标识符类型。

相关：

：//dba.stackexchange.com/questions/36979/retrieving-all-pk-and-fk-with-pgadmin/37068#37068>检索所有PK和FK

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.

解决方案

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:

• PostgreSQL drop constraint with unknown name
• Retrieving all PK and FK

这篇关于使用表，字段和模式名称查找引用的表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！