記事一覧

PostgreSQL のシリアル型のシーケンスを調整する

 

  
目次     



  

PostgreSQLのシリアル型のシーケンスを調整する

ハロー、みなさん。エジソンです。 

今回は、PostgreSQLにおいてシリアル型で定義したシーケンスを一括で調整する方法を紹介します。
テーブルにシリアル型を用いると、Insert 時に自動で番号を採番してくれます。

ただし、初期データなどを手動で Insert する際に、自前で数値を指定することもできるため、その場合はシーケンス値が乱れてしまい、次回 Insert を実行すると一意制約エラーなどが発生することが有ります。

このようなケースは開発途中によくあることなので、シーケンスを自動で調整するスクリプトのようなものがほしいところです。

PostgreSQL 9.6で検証しました。


ストアドを書いてみた

大前提として、ユーザー定義テーブルの全てにおいて、id という名前のカラムが必ず存在し、データ型がシリアルであるとします。

リスト1:シーケンス調整ストアド
DROP FUNCTION IF EXISTS FNC_ALL_SEQUENCE_ADJUST();
CREATE OR REPLACE FUNCTION FNC_ALL_SEQUENCE_ADJUST() returns int as
$$
DECLARE
    cu refcursor;
    rec record;
BEGIN
    OPEN cu FOR
        SELECT 'SELECT SETVAL(''' || table_name || '_id_seq'', (select max(id) from ' || table_name || '));' AS sql
        FROM information_schema.tables
        WHERE table_type <> 'VIEW' and table_schema = 'public';

    LOOP
        FETCH cu INTO rec;
        IF not found THEN
            EXIT;
        END IF;

        BEGIN
            EXECUTE rec.sql;
            raise info 'sql = %', rec.sql;

        EXCEPTION WHEN undefined_function or undefined_table THEN
        END;

    END LOOP;
    CLOSE cu;
    return 0;
END;
$$
language 'plpgsql'
;

select FNC_ALL_SEQUENCE_ADJUST();

DROP FUNCTION FNC_ALL_SEQUENCE_ADJUST();

リスト1を実行すると、全てのテーブルのシーケンスを調整できます。本ストアドでは、ストアド自身を処理の先頭で(存在していれば)削除し、次にストアドを生成し自身を実行…、最後にまた削除するという流れになります。

今思い付きましたが、本ストアドでは存在するテーブルを起点にループしていますが、存在するシーケンスオブジェクトを起点にループさせて、シーケンスを調整するという流れのほうが汎用性があるかもしれません。こうした場合は、シーケンスが id という名前で無くても構わなくなります。

関連記事

このエントリーをはてなブックマークに追加

コメント

コメントの投稿

非公開コメント

プロフィール

EZOLABブログへようこそ。
EZOLABは、札幌のソフトウェア会社です。

http://ezolab.co.jp

ezolab