使用表、字段和模式名称查找引用的表名称 [英] 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)

例如:

Schema1.TableA
  Id (Integer, PK)  
  Name varchar


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

我需要将 A_Id、TableB 和 Schema2 传递给函数并得到 Schema1.TableA 作为结果.

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

我正在使用 Postgres 8.3.

I am using Postgres 8.3.

推荐答案

如果您不需要将它移植到另一个 RDBMS，那么使用 中的目录表会更快更简单 code>pg_catalog 而不是标准的信息架构:

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;

返回:

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

注意事项

• 另外两列仅用于定位.根据您的 Q，您只需要第一列.

  Notes

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

    这将返回 all 由涉及给定列名的所有外键引用的表 - 包括多个列上的 FK 约束.

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

    根据当前 search_path.该名称也会在需要的地方(非法或大写字符、保留字等)自动转义.

    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.

    查看 pg_constraint 和 pg_attribute 在手册中.还有更多关于对象标识符类型的信息.

    相关:

    • PostgreSQL 使用未知名称删除约束
    • 检索所有主键和外键

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