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

问题描述

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

例如:

  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约束。

  • 该名称根据当前 。




p>查看和。还有更多关于。


相关:




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:

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

08-05 12:32