我可以运行以下行:
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
更多推荐
获取表和列“拥有”一个序列
发布评论