How to get info about relations between system tables?
One my customer asked me "what tables holds references to table pg_roles"?
The reply is simple - none. pg_roles is view. But this view uses very important table pg_authid. For custom tables we can use a query:
SELECT conname, conrelid::pg_catalog.regclass AS ontable, pg_catalog.pg_get_constraintdef(oid, true) AS condef FROM pg_catalog.pg_constraint c WHERE confrelid = 'a'::pg_catalog.regclass AND contype = 'f' ORDER BY conname; ┌─────────────┬─────────┬─────────────────────────────────────┐ │ conname │ ontable │ condef │ ╞═════════════╪═════════╪═════════════════════════════════════╡ │ b_a_id_fkey │ b │ FOREIGN KEY (a_id) REFERENCES a(id) │ └─────────────┴─────────┴─────────────────────────────────────┘ (1 row)But this method doesn't work for system tables. These tables doesn't use explicitly defined foreign keys. We should to use different method. The system function pg_get_catalog_foreign_keys returns all referencies between system tables, and we can filter result:
SELECT * FROM pg_get_catalog_foreign_keys() WHERE pktable = 'pg_authid'::regclass; ┌─────────────────────────┬──────────────┬───────────┬────────┬──────────┬────────┐ │ fktable │ fkcols │ pktable │ pkcols │ is_array │ is_opt │ ╞═════════════════════════╪══════════════╪═══════════╪════════╪══════════╪════════╡ │ pg_proc │ {proowner} │ pg_authid │ {oid} │ f │ f │ │ pg_type │ {typowner} │ pg_authid │ {oid} │ f │ f │ │ pg_class │ {relowner} │ pg_authid │ {oid} │ f │ f │ │ pg_operator │ {oprowner} │ pg_authid │ {oid} │ f │ f │ │ pg_opfamily │ {opfowner} │ pg_authid │ {oid} │ f │ f │ │ pg_opclass │ {opcowner} │ pg_authid │ {oid} │ f │ f │ │ pg_language │ {lanowner} │ pg_authid │ {oid} │ f │ f │ │ pg_largeobject_metadata │ {lomowner} │ pg_authid │ {oid} │ f │ f │ │ pg_statistic_ext │ {stxowner} │ pg_authid │ {oid} │ f │ f │ │ pg_event_trigger │ {evtowner} │ pg_authid │ {oid} │ f │ f │ │ pg_namespace │ {nspowner} │ pg_authid │ {oid} │ f │ f │ │ pg_conversion │ {conowner} │ pg_authid │ {oid} │ f │ f │ │ pg_database │ {datdba} │ pg_authid │ {oid} │ f │ f │ │ pg_db_role_setting │ {setrole} │ pg_authid │ {oid} │ f │ t │ │ pg_tablespace │ {spcowner} │ pg_authid │ {oid} │ f │ f │ │ pg_auth_members │ {roleid} │ pg_authid │ {oid} │ f │ f │ │ pg_auth_members │ {member} │ pg_authid │ {oid} │ f │ f │ │ pg_auth_members │ {grantor} │ pg_authid │ {oid} │ f │ f │ │ pg_ts_config │ {cfgowner} │ pg_authid │ {oid} │ f │ f │ │ pg_ts_dict │ {dictowner} │ pg_authid │ {oid} │ f │ f │ │ pg_extension │ {extowner} │ pg_authid │ {oid} │ f │ f │ │ pg_foreign_data_wrapper │ {fdwowner} │ pg_authid │ {oid} │ f │ f │ │ pg_foreign_server │ {srvowner} │ pg_authid │ {oid} │ f │ f │ │ pg_user_mapping │ {umuser} │ pg_authid │ {oid} │ f │ t │ │ pg_policy │ {polroles} │ pg_authid │ {oid} │ t │ t │ │ pg_default_acl │ {defaclrole} │ pg_authid │ {oid} │ f │ f │ │ pg_collation │ {collowner} │ pg_authid │ {oid} │ f │ f │ │ pg_publication │ {pubowner} │ pg_authid │ {oid} │ f │ f │ │ pg_subscription │ {subowner} │ pg_authid │ {oid} │ f │ f │ │ pg_variable │ {varowner} │ pg_authid │ {oid} │ f │ f │ └─────────────────────────┴──────────────┴───────────┴────────┴──────────┴────────┘ (30 rows)
1 Comments:
Cool, good to know that!
Post a Comment
Subscribe to Post Comments [Atom]
<< Home