概要
PostgreSQL12 からGenerated Columnが使えるようになりましたね。
私はdateを含んだ式を仕込んでおこうと思いました。
しかし、 エラーが出て駄目でした。
exampleで使っているものは仮です。実用から読み替えて書いています。
ALTER TABLE diaries
ADD IF NOT EXISTS subject_date varchar(255)
GENERATED ALWAYS AS (subject::text || '__' || date(timezone('JST', published_on)) STORED;
ERROR: 生成式は不変ではありません
TL;DR;
CREATE FUNCTION my_immutable_date(date) RETURNS TEXT LANGUAGE sql IMMUTABLE
AS $$
SELECT date(timezone('JST', $1))::text
$$;
ALTER TABLE diaries
ADD IF NOT EXISTS subject_date varchar(255)
GENERATED ALWAYS AS (subject::text || '__' || my_immutable_date(published_on)) STORED;
調査
dateをStringに変換するとき、タイムゾーン設定の関係でImmutableとして認識してくれないようです。
StackOverfllowを見ると、タイムゾーンを指定した変換をすると不変関数として動作してGenerated Columnが動くらしいですが、私の環境では動きませんでした。
解決法
https://www.2ndquadrant.com/en/blog/generated-columns-in-postgresql-12/
強そうな人の解説を見ました。
IMMUTABLEな関数を定義して、その中で変換をするラッパー関数を定義すれば動作するとのことです。
CREATE FUNCTION my_immutable_date(date) RETURNS TEXT LANGUAGE sql IMMUTABLE
AS $$
SELECT date(timezone('JST', $1))::text
$$;
ALTER TABLE diaries
ADD IF NOT EXISTS subject_date varchar(255)
GENERATED ALWAYS AS (subject::text || '__' || my_immutable_date(published_on)) STORED;