基本はpg_classを参照すればオブジェクト一覧を取得できるのですが、スキーマ名や、所有者名までを調べたい場合pg_namespaceやpg_rolesまで結合しないといけない点少し不便ですね。以下はsooniスキーマに存在するオブジェクト一覧を取得しています。
オブジェクト一覧取得SQL
select r.rolname as owner ,n.nspname as schema ,relname , case when c.relkind = 'i' then 'index' when c.relkind = 'm' then 'materialized view' when c.relkind = 'r' then 'table' when c.relkind = 'S' then 'sequence' when c.relkind = 't' then 'TOAST' when c.relkind = 'v' then 'view' when c.relkind = 'p' then 'partitioned table' when c.relkind = 'I' then 'partitioned index' when c.relkind = 'f' then 'foreign table' else concat(c.relkind,'') end relkind_dec ,case when c.relpersistence = 'p' then '永続' when c.relpersistence = 'u' then 'ログ無し' when c.relpersistence = 't' then '一時' else concat(c.relpersistence,'') end relpersistence_dec ,pg_catalog.array_to_string(relacl,',') relacl from pg_class c inner join pg_namespace n on c.relnamespace = n.oid inner join pg_roles r on c.relowner = r.oid where 1=1 and r.rolname ='sooni' -- オブジェクトオーナー --and c.relkind = 'r' -- オブジェクトタイプ --and relname = 'yasai' -- オブジェクト名 order by owner,schema,relname ;
オブジェクト一覧取得例
owner | schema | relname | relkind_dec | relpersistence_dec | relacl
-------+----------+-------------------------------+-------------------+--------------------+------------------------------------------
sooni | public | kudamono | foreign table | 永続 |
sooni | public | ptbl01 | table | 永続 |
sooni | public | tbl9001 | table | 永続 |
sooni | public | yasai | table | 永続 |
sooni | sooni | addres_list | partitioned table | 永続 |
sooni | sooni | addres_list_idx01 | partitioned index | 永続 |
sooni | sooni | addres_list_idx02 | partitioned index | 永続 |
sooni | sooni | address | table | 永続 |
sooni | sooni | address_gaiku | view | 永続 | sooni=arwdDxt/sooni,online_user=r/sooni
sooni | sooni | addrestest | table | 永続 |
sooni | sooni | chiba | table | 永続 |
sooni | sooni | chiba_city_idx | index | 永続 |
sooni | sooni | chiba_prefectures_city_idx | index | 永続 |
sooni | sooni | ex02 | table | 永続 | sooni=arwdDxt/sooni,online_user=ar/sooni
sooni | sooni | ex04 | table | 永続 | sooni=arwdDxt/sooni,online_user=ar/sooni
sooni | sooni | ex500 | table | ログ無し |
sooni | sooni | hokkaido | table | 永続 |
sooni | sooni | hokkaido_city_idx | index | 永続 |
sooni | sooni | hokkaido_prefectures_city_idx | index | 永続 |
sooni | sooni | idx01_ex400 | index | 永続 |
sooni | sooni | intable | table | 永続 |
sooni | sooni | kokudo_address | table | 永続 |
sooni | sooni | kokudo_address_chome | table | 永続 |
sooni | sooni | kokudo_address_gaiku | table | 永続 |
sooni | sooni | kyoto | table | 永続 |
sooni | sooni | kyoto_city_idx | index | 永続 |
sooni | sooni | kyoto_prefectures_city_idx | index | 永続 |
sooni | sooni | logging | table | 永続 |
sooni | sooni | logging_logid_seq | sequence | 永続 |
sooni | sooni | mst001 | table | 永続 |
sooni | sooni | note_pc | table | 永続 |
sooni | sooni | okinawa | table | 永続 |
sooni | sooni | okinawa_city_idx | index | 永続 |
sooni | sooni | okinawa_prefectures_city_idx | index | 永続 |
sooni | sooni | prdb_kudamono | view | 永続 |
sooni | sooni | saitama | table | 永続 |
sooni | sooni | saitama_city_idx | index | 永続 |
sooni | sooni | saitama_prefectures_city_idx | index | 永続 |
(38 rows)
myposdb=#
