I can run the following line:
ALTER SEQUENCE seqName OWNED BY table.id;
How can I get the 'owner' set by OWNED BY
for a sequence (in this case: table.id
)?
ALTER SEQUENCE seqName OWNED BY table.id;
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
AND d.classid = 'pg_class'::regclass;
Double quotes (""
) are only needed for otherwise illegal names (mixed case, reserved words, ...).
No need to assert that refclassid
is of type regclass
since the join to pg_attribute
does that automatically.
No need to assert that the sequence is a sequence since schema-qualified object names are unique across the database.
No need to join to pg_class
or pg_namespace
at all.
The schema name is only needed to disambiguate or if it's not in the 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.
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 where necessary to be unambiguous in 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