PostgreSQL

PostgreSQL オブジェクト一覧を確認する(アクセス権限含む)

基本は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=#

各種オブジェクト毎の一覧はこちらから

スポンサーリンク
コピペで使う