PostgreSQL VIEW一覧

pg_viewsにてVIEW一覧を確認する

VIEW一覧出力SQL

SELECT schemaname,viewname,viewowner,definition
FROM pg_views v
where v.schemaname ='sooni'
ORDER BY viewname
;

VIEW一覧出力例

myposdb=# SELECT schemaname,viewname,viewowner,definition
myposdb-# FROM pg_views v
myposdb-# where v.schemaname ='sooni'
myposdb-# ORDER BY viewname
myposdb-# ;
 schemaname |   viewname    | viewowner |                   definition
------------+---------------+-----------+------------------------------------------------
 sooni      | address_gaiku | sooni     |  SELECT kokudo_address_gaiku.lno,             +
            |               |           |     kokudo_address_gaiku.prefectures_name,    +
            |               |           |     kokudo_address_gaiku.city_name,           +
            |               |           |     kokudo_address_gaiku.oaza_town_chome_name,+
            |               |           |     kokudo_address_gaiku.block_number         +
            |               |           |    FROM kokudo_address_gaiku;
(1 行)


myposdb=#

VIEWを構成するテーブルのdropを試みるとエラーが出ます

-- VIEWに使われているテーブルの削除を試みると次のようなメッセージが出力されます
-- 
myposdb=# drop table kokudo_address_gaiku
myposdb-# ;
ERROR:  cannot drop table kokudo_address_gaiku because other objects depend on it
DETAIL:  view address_gaiku depends on table kokudo_address_gaiku
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
myposdb=#