我有一个要求,我需要使用该字段名、表名(该字段所在的位置)和架构,通过表(外键表)中的特定字段查找引用的表名(主键表名)名称(表和字段所在的位置)
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 使用未知名称删除约束
- 检索所有主键和外键
更多推荐
使用表、字段和模式名称查找引用的表名称
发布评论