PostgreSQL の UPDATE トリガで時刻を自動設定できなかった話

今回はいつになく簡易版でお送りします。

ようは下のようなトリガを書いても期待通りに動かなかった話です。

該当カラムの定義は updatedAt TIMESTAMP DEFAULT current_timestamp NOT NULL です。
MySQL の時は updatedAt DATETIME DEFAULT current_timestamp ON UPDATE current_timestamp NOT NULL と書けばトリガ不要で自動更新してくれますね。

-- UPDATE 文の実行時に updatedAt カラムを
-- 現在時刻に設定してくれるトリガ(PostgreSQL 用)
-- 動作不良版
create or replace function set_updated_at() returns trigger as $function$
begin
if new.updatedAt is null then
new.updatedAt := current_timestamp;
end if;
return new;
end;
$function$ language plpgsql;

create trigger auto_set_updated_at before update on SomeTable for each row
execute procedure set_updated_at();

これで SomeTable の UPDATE 文実行時に updatedAt カラムをその時の時刻で更新してくれるはずでした。
ところが、どうやら SET 句に updatedAt カラムを指定しない場合でも new.updatedAt が NULL になってくれないようです。(例えば UPDATE SomeTable SET OtherCol = 1)

で、色々試して、とりあえず動いたのが次のバージョンです。
ちなみに、明示的に SET 句で値を指定した時(updatedAt = ‘2016-06-06 12:00’ 等)は、そちらを使います。

-- UPDATE 文の実行時に updatedAt カラムを
-- 現在時刻に設定してくれるトリガ(PostgreSQL 用)
-- いちおう動作版
create or replace function set_updated_at() returns trigger as $function$
begin
-- 次の行の条件を変更
if new.updatedAt is null OR new.updatedAt = old.updatedAt then
new.updatedAt := current_timestamp;
end if;
return new;
end;
$function$ language plpgsql;

動いたのはいいんですが、なぜ new.updatedAt が NULL じゃないのか気になって仕方ありません。
どなたか理由をご存知でしたら、ぜひともご教示ください。

postgresql のストアドプロシージャを移行する

postgreSQL のデータをスキーマ間で移行する作業の時に調べ回ることになったのでメモ。

プロシージャ(かトリガのどちらか)の一覧は「\df」で取得できる所までは、まあ大丈夫とする。
実際のソースは「SELECT prcsrc FROM pg_proc WHERE prcname = 'procedure_name'」で取得できる。

とは言え、これではソースのみであり、完全な CREATE FUNCTION 文ではない。
そこで pg_dump を利用する。

スキーマをダンプする時にオブジェクトを一緒にダンプするように、次の SQL 文を使う。
pg_dump --schema-only --oids -Fp db_name > filename」(必要なら -h、-U、-p 等も使う)
これで希望の CREATE FUNCTION 文を入手できる。後はエディタでちゃっちゃと編集すればいい。

もしかすると、適当な空のテーブルを作っておいて「-t tablename」を指定すると、余計なスキーマ情報が入らないかもしれない。…と思って試してみたら駄目だった。

あとは psql で入って「\i filename」で読み込めば OK となる。