使用表、字段和模式名称查找引用的表名称

本文介绍了使用表、字段和模式名称查找引用的表名称的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个要求，我需要使用该字段名、表名(该字段所在的位置)和架构，通过表(外键表)中的特定字段查找引用的表名(主键表名)名称(表和字段所在的位置)

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 使用未知名称删除约束
    • 检索所有主键和外键