Database 使用Postgres获取带有表架构的外键

发布于06月12日

我是新来的波斯格雷斯.我找到了一个SQL，它几乎满足了我的需求，但我还没有弄清楚如何将表所有者作为结果集的一部分，以及如何在WHERE子句中使用表所有者.我将注释放在我需要的专栏引用的位置.以下是我的代码:

SELECT (select  r.relname from pg_class r where r.oid = c.confrelid) as base_table,
       a.attname as base_col,
       --base table schema
       (select r.relname from pg_class r where r.oid = c.conrelid) as referencing_table,
       UNNEST((select array_agg(attname) from pg_attribute where attrelid = c.conrelid and array[attnum] <@ c.conkey)) as referencing_col
       -- referenceing table schema       
  FROM pg_constraint c join pg_attribute a on c.confrelid=a.attrelid and a.attnum = ANY(confkey)
 WHERE c.confrelid = (select oid from pg_class where relname = 'table_name')
    -- and table_schema = 'schema_name'
   AND c.confrelid!=c.conrelid;

任何帮助都将不胜感激.

推荐答案

您的查询不合理，在复合外键的情况下，它返回太多(重复的)行.我建议在这些情况下返回列名列表，使用string_agg().联接pg_class到pg_constraint以获取表的所有者名称:

select
    cb.relname as base_table,
    cb.relowner::regrole as base_owner,
    (select string_agg(attname, ',') 
        from pg_attribute 
        where attrelid = confrelid and attnum = any(confkey)
    ) as base_columns,
    cr.relname as referencing_table,
    cr.relowner::regrole as reftable_owner,
    (select string_agg(attname, ',') 
        from pg_attribute 
        where attrelid = conrelid and attnum = any(conkey)
    ) as ref_columns
from pg_constraint c 
join pg_class cb on cb.oid = c.confrelid
join pg_class cr on cr.oid = c.conrelid
where c.confrelid = 'public.master'::regclass
-- sample owner condition
and cb.relowner = 'postgres'::regrole

在db<>fiddle.分钟内进行测试

最新消息.系统目录pg_class的列relnamespace包含关于表模式的信息:

select
    cb.relname as base_table,
    cb.relnamespace::regnamespace as base_schema,
    (select string_agg(attname, ',') 
        from pg_attribute 
        where attrelid = confrelid and attnum = any(confkey)
    ) as base_columns,
    cr.relname as referencing_table,
    cr.relnamespace::regnamespace as reftable_schema,
    (select string_agg(attname, ',') 
        from pg_attribute 
        where attrelid = conrelid and attnum = any(conkey)
    ) as ref_columns
from pg_constraint c 
join pg_class cb on cb.oid = c.confrelid
join pg_class cr on cr.oid = c.conrelid
-- sample schema condition
where cb.relnamespace = 'public'::regnamespace;

在db<>fiddle.分钟内进行测试

Database 使用Postgres获取带有表架构的外键

推荐答案

Database相关问答推荐

找不到复制副本集

powerapps 中的 ClearCollect 未按预期收集数据

如何在保持相同 Flyway 校验和的同时更正语法？

我们可以在 CnosDB 中的单个数据库中创建的标签数量是否有限制？

实体关系图. IS A 关系如何转换为table表？

优雅地处理 EJB/JPA 环境中的约束冲突？

使用 PostgresQL 判断现有列的约束

SQL 中的 LIMIT 语句使用很普遍？

管理数据库中的产品计数

PHP - 数据库抽象层使用静态类与单例对象？

数据库设计—应该避免一对一的关系吗？

mySQL 复制是否具有即时数据一致性？

我应该如何使用 MySQL 构建我的设置表？

找不到类型或命名空间名称SQLConnection

PostgreSQL 哈希索引

如何处理数据库中用户的身份验证/授权？

sqlite 时间戳格式化

Rails 控制台 - 查找在某天创建的位置

将 NULL 插入 MySQL 时间戳

如果值不为空，则用于更新数据库的 Sql 查询？