【PostgreSQL】VIEWやPROCEDUREを一括削除するSQL

はじめに

とあるスキーマのVIEWだけを全削除したいときに作った雑なSQLについて書き残しておこうと思います。

ワンクリックで全消しとはいきませんが、カスタマイズも簡単で事前にどの表が消えるのか確認しやすいので、意外と使えるんじゃないですかね？

環境

・Windows10
・PostgreSQL 16.2
・A5M2 2.20.1

VIVEを削除するSQL

作ったSQLがコレ。
A5M2でCREATE PROCEDUREせずに実行したかったので無名ブロックのDO$$ $$を使用していますが、コマンドプロンプトでpsqlを使って実行する場合は消した方がよさげ？

VIVE一括削除.sql

DO
$outer$
DECLARE
  -- 変数宣言
  sSqlText  text;
  sSchema   varchar = 'sudare';  -- 削除したい対象があるスキーマ名
  
  -- カーソルの宣言
  DELETE_VIEW_CS CURSOR FOR
  select viewname
  from pg_catalog.pg_views 
  where schemaname = sSchema;

-- 実行開始
BEGIN
    FOR DVCS IN DELETE_VIEW_CS LOOP
     -- SQL生成
     sSqlText := 'DROP VIEW ' || DVCS.viewname || ' CASCADE';
     
     -- SQL実行
     execute sSqlText;
     
     -- 明示的コミット
     COMMIT;
    END LOOP;
END;
$outer$ language plpgsql;

CASCADEで依存関係にある表を自動的に削除している影響で view "" does not exist というエラーで止まる可能性があります。が、明示的なCOMMITで止まる前の処理は反映されているので処理された分は消去されています。
何度も実行すれば、いずれVIEWが全部消えるでしょう（ゴリ押し）

VIVEを削除するSQLですので、事前にカーソル宣言部のSELECT文を実行するなどして消去されるVIVEを確認しておきましょう。
間違って消しちゃっても責任取れないよ～。

説明

カーソル宣言

カーソルを宣言しています。
ここでSELECTされるviewnameが削除されます。カスタマイズしたい場合はWhere句をイジったりするといいです。
実行する前にこのSELECT文だけ個別に実行することで消去される表を事前に確認できます。ここがお気に入りポイントですね。

ちなみに、参照しているpg_viewsはシステムカタログといって内部的な情報を格納している場所です。
詳しく知らない方は select * from pg_catalog.pg_views で一度見てみるといいでしょう。

カーソル宣言

-- カーソルの宣言
[カーソル名] CURSOR FOR
 select viewname
 from pg_catalog.pg_views 
 where schemaname = [スキーマ名;]
 [その他条件];

実行部分

カーソル結果に対してLOOPすることで 【SQL文を生成→DROP実行→コミット】 を繰り返し、VIEWを削除しています。
CASCADEで依存関係にある表を自動削除しているため、『表が見つかりません!（CASCADEで先に削除した為）』というエラーが出る可能性があります。ですが、明示的COMMITのお陰でロールバックせず実行された処理は反映されています。

もっとスマートにSQLを書けたらエラーに掛からずに済むのかもしれませんが…。

DROP VIEW実行

-- 実行開始
BEGIN
    FOR [レコード型変数名] IN [カーソル名] LOOP
     -- SQL生成
     sSqlText := 'DROP VIEW ' || [レコード型変数名].viewname || ' CASCADE';
     
     -- SQL実行
     execute sSqlText;
     
     -- 明示的コミット
     COMMIT;
    END LOOP;
END;

FOR RECORD型変数名 IN カーソル名 LOOP 処理内容 END LOOP;の形にすることでカーソルを自動で開いて自動で閉じることができます。

その他応用（TABLE・PROCEDURE・FUNCTION）

テーブルもこんな感じやればできると思います。CASCADEで依存関係にあるVIEWも消えるので注意してね。

TABLE版

DO
$outer$
DECLARE
  sSqlText  text;
  sSchema   varchar = 'sudare'; 
  
  -- カーソル宣言
  DELETE_TABLE_CS CURSOR FOR
  select tablename
  from pg_catalog.pg_tables 
  where schemaname = sSchema;
  
 BEGIN
    FOR DTCS IN DELETE_TABLE_CS LOOP
    
     sSqlText := 'DROP TABLE ' || DTCS.tablename || ' CASCADE';
     execute sSqlText;
     COMMIT;
    END LOOP;
 END;
$outer$ language plpgsql;

プロシージャとファンクション版はこんな感じ？実際に動かしてないから上手くいくかは知らないです。

実行は自己責任でオナシャス。

PROCEDURE版

DO
$outer$
DECLARE
  sSqlText  text;
　sSchema   varchar = 'sudare'; 

  
  -- カーソル宣言
  DELETE_PRO_CS CURSOR FOR
  select proname
  from pg_catalog.pg_proc 
  where pronamespace = (select oid from pg_catalog.pg_namespace where nspname = sSchema)
    and prokind      = 'p';       -- pはPROCEDURE fはFUNCTION
  
 BEGIN
    FOR DPCS IN DELETE_PRO_CS LOOP
    
     sSqlText := 'DROP PROCEDURE ' || DPCS.proname || ' CASCADE';
     execute sSqlText;
     COMMIT;
    END LOOP;
 END;
$outer$ language plpgsql;

FUNCTION版

DO
$outer$
DECLARE
  sSqlText  text;
  sSchema   varchar = 'sudare'; 

  
  -- カーソル宣言
  DELETE_FUN_CS CURSOR FOR
  select proname
  from pg_catalog.pg_proc 
  where pronamespace = (select oid from pg_catalog.pg_namespace where nspname = sSchema)
    and prokind      = 'f';       -- pはPROCEDURE fはFUNCTION
  
 BEGIN
    FOR DFCS IN DELETE_FUN_CS LOOP
    
     sSqlText := 'DROP FUNCTION ' || DFCS.proname || ' CASCADE';
     execute sSqlText;
     COMMIT;
    END LOOP;
 END;
$outer$ language plpgsql;