获取表和列“拥有”一个序列 [英] Get table and column "owning" a sequence

问题描述

我可以运行以下行：

ALTER SEQUENCE seqName OWNED BY table.id;

我如何获得由 OWNED BY 一个序列（在这种情况下是 table.id ）？

How can I get the 'owner' set by OWNED BY for a sequence (in this case: table.id)?

推荐答案

获取拥有表和列

Get the "owning" table and column

ALTER SEQUENCE seqName OWNED BY table.id;

您的 ALTER SEQUENCE 系统目录 pg_depend 与依赖类型（ deptype ）'a'和 refobjsubid 大于 0 ，指向pg_attribute中的属性编号（ attnum ）。有了这个知识，你可以设计一个简单的查询：

Your ALTER SEQUENCE statement causes an entry in the system catalog pg_depend with the dependency type (deptype) 'a' and a refobjsubid greater than 0, pointing to the attribute number (attnum) in pg_attribute. With that knowledge you can devise a simple query:

SELECT d.refobjid::regclass, a.attname
FROM   pg_depend    d
JOIN   pg_attribute a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
WHERE  d.objid = 'public."seqName"'::regclass  -- your sequence here
AND    d.refobjsubid > 0;

• 双引号（ ）仅用于其他非法名称（混合大小写，保留字，...）。

  • Double quotes ("") are only needed for otherwise illegal names (mixed case, reserved words, ...).

    不需要声明 classid 和 refclassid 的类型为 regclass ，因为加入 pg_attribute 自动执行。
    
    无需声明序列是序列，因为名称是唯一的。
    
    无需加入 pg_class 或 pg_namespace 。

    No need to assert that classid and refclassid are of type regclass since the join to pg_attribute does that automatically.
    No need to assert that the sequence is a sequence since the name is unique.
    No need to join to pg_class or pg_namespace at all.

    模式名称只需要消除歧义，或者不在 search_path 中。

    The schema name is only needed to disambiguate or if it's not in the search_path.

    同一个表可以在多个模式中使用名称（或序列名称）。转换到对象标识符类型 regclass 观察当前的 search_path ，以选择最佳匹配，如果省略模式资格。如果表格不可见，您会收到一条错误消息。

    The same table name (or sequence name for that matter) can be used in multiple schemas. A cast to the object identifier type regclass observes the current search_path to pick the best match if you omit the schema qualification. If the table is not visible, you get an error message.

    另外，一个 regclass 被自动显示为 text 给用户。 （如果没有，则转换为文本。）模式名称自动添加，如果它不是 search_path中的第一个匹配项，保证您的会话无歧义的输出。

    What's more, a regclass type is displayed as text to the user automatically. (If not, cast to text.) The schema-name is prepended automatically, if it's not the first match in the search_path, guaranteeing an unambiguous output for your session.

    根据请求获取角色拥有特定的顺序：

    To get the role owning a specific sequence, as requested:

    SELECT c.relname, u.usename 
    FROM   pg_class c
    JOIN   pg_user  u ON u.usesysid  = c.relowner
    WHERE  c.oid = '"seqName"'::regclass;  -- your sequence here
    

    这篇关于获取表和列“拥有”一个序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！