PostgreSQL PL/pgSQL の開発

UCIDM が呼び出すオブジェクト を PostgreSQL の PL/pgSQL で実装する方法を説明します。CREATE PROCEDURE を使うため、PostgreSQL 11 以降が対象です。

呼び出しの仕様そのものは ストアドプロシージャ／ファンクション にまとめています。本ページは PostgreSQL 固有の書き方に絞ります。

• UCIDM が発行する SQL
• 実装例の前提
• ユーザー反映プロシージャ
• ユーザー取得ファンクション
• グループの実装
• メンバーの実装
• psql で動作を確認する
• 管理画面に登録する
• つまずきやすい点
  • 引数の型は json 固定
  • $$ の入れ子に注意する
  • 型変換の失敗は例外になる
  • エラーメッセージには関数名と行番号が付く
  • プロシージャに COMMIT を書かない
  • 識別子は小文字に畳まれる

UCIDM が発行する SQL

実装の制約は、UCIDM が発行する SQL から決まります。PostgreSQL に対しては次の3種類を発行します。

-- 反映プロシージャの呼び出し
CALL ucidm_apply_user($1);

-- 取得ファンクションの呼び出し
SELECT ucidm_get_user($1);

-- インストール前の削除 (引数の型で修飾される)
DROP PROCEDURE IF EXISTS ucidm_apply_user(json);
DROP FUNCTION IF EXISTS ucidm_get_user(json);

ここから、PostgreSQL での実装は次の形に決まります。

• 引数の型は json にします。jsonb や text にすると DROP 文と一致しなくなります
• 引数はちょうど1つです
• 取得ファンクションの戻り値は SELECT から参照できるスカラー型にします
• オブジェクトは、接続時の search_path の先頭のスキーマに作られます

引数の型は特に注意してください。jsonb で作成すると、UCIDM の DROP は (json) を探すため何も削除しません。定義を変更したときに古いオブジェクトが残り、同じ名前で引数の型だけが違う関数が2つできて呼び出しが曖昧になります。

実装例の前提

社員管理の既存スキーマに ID 連携する例で説明します。Oracle PL/SQL の開発 と同じスキーマを PostgreSQL の型で用意します。

-- ユーザーの基本情報
CREATE TABLE emp_user (
  user_name    text PRIMARY KEY,
  display_name text NOT NULL,
  status       text,
  emp_no       int,
  pwd_changed  timestamp,
  dept_code    text
);

-- メールアドレスは別テーブル
CREATE TABLE emp_contact (
  user_name text PRIMARY KEY,
  email     text
);

-- パスワードも別テーブル
CREATE TABLE emp_cred (
  user_name text PRIMARY KEY,
  password  text
);

-- グループは連番の代理キーが主キー
CREATE TABLE emp_group_seq (
  id         serial PRIMARY KEY,
  primary_id text UNIQUE
);
CREATE TABLE emp_group (
  id    int PRIMARY KEY,
  label text
);
CREATE TABLE emp_member (
  group_name text,
  user_name  text,
  PRIMARY KEY (group_name, user_name)
);

-- 部署名から部署コードへの変換マスタ
CREATE TABLE emp_dept_code (
  name text PRIMARY KEY,
  code text
);
INSERT INTO emp_dept_code(name, code)
  VALUES ('営業部', 'D001'), ('開発部', 'D002'), ('人事部', 'D003');

属性マッピングも Oracle の例と同じです。左の列が反映プロシージャの attrs のキーになります。

グループの属性マッピングは label に cn を割り当てます。

最後の ucidmUserEnabled は、JSON の enabled を解決するために必要です。動作設定で「ユーザーステータス設定情報を使用する」を off にした場合、UCIDM は「ステータス用属性名」に指定した名前をマッピング後の属性から探します。属性マッピングにこの行がないと enabled が解決されず、JSON に含まれません。「ユーザーステータス設定情報を使用する」を on にした場合はマッピングは不要です。

ユーザー反映プロシージャ

opCode で4つの操作を分岐します。

CREATE PROCEDURE ucidm_apply_user(p json) LANGUAGE plpgsql AS $$
DECLARE
  op  text := p->>'opCode';
  pid text := p->>'primaryID';
BEGIN
  IF op = 'I' THEN
    INSERT INTO emp_user(user_name, display_name, status, emp_no, pwd_changed, dept_code)
      VALUES (pid,
              p#>>'{attrs,cn}',
              CASE WHEN (p->>'enabled')::boolean THEN 'active' ELSE 'inactive' END,
              (p#>>'{attrs,empNo}')::int,
              (to_timestamp(p#>>'{attrs,pwdChanged}', 'YYYYMMDDHH24MISS"Z"'))::timestamp,
              (SELECT code FROM emp_dept_code WHERE name = p#>>'{attrs,department}'));
    INSERT INTO emp_contact(user_name, email) VALUES (pid, p#>>'{attrs,mail}');

  ELSIF op = 'U' THEN
    UPDATE emp_user SET
      display_name = COALESCE(p#>>'{attrs,cn}', display_name),
      emp_no       = COALESCE((p#>>'{attrs,empNo}')::int, emp_no),
      pwd_changed  = COALESCE((to_timestamp(p#>>'{attrs,pwdChanged}', 'YYYYMMDDHH24MISS"Z"'))::timestamp,
                              pwd_changed),
      dept_code    = COALESCE((SELECT code FROM emp_dept_code
                                 WHERE name = p#>>'{attrs,department}'), dept_code),
      status       = CASE WHEN p->>'enabled' IS NOT NULL
                          THEN (CASE WHEN (p->>'enabled')::boolean THEN 'active' ELSE 'inactive' END)
                          ELSE status END
      WHERE user_name = pid;
    IF p#>>'{attrs,mail}' IS NOT NULL THEN
      INSERT INTO emp_contact(user_name, email) VALUES (pid, p#>>'{attrs,mail}')
        ON CONFLICT (user_name) DO UPDATE SET email = EXCLUDED.email;
    END IF;

  ELSIF op = 'P' THEN
    INSERT INTO emp_cred(user_name, password) VALUES (pid, p#>>'{attrs,password}')
      ON CONFLICT (user_name) DO UPDATE SET password = EXCLUDED.password;

  ELSIF op = 'D' THEN
    DELETE FROM emp_contact WHERE user_name = pid;
    DELETE FROM emp_cred WHERE user_name = pid;
    DELETE FROM emp_user WHERE user_name = pid;
  END IF;
END $$;

分岐ごとの要点を順に見ます。

I では、attrs の文字列をキャスト演算子でカラムの型に変換しています。pwdChanged の元になる LDAP の pwdChangedTime は 20260220213129Z という generalizedTime 形式なので、書式テンプレート YYYYMMDDHH24MISS"Z" で to_timestamp に解釈させます。to_timestamp の戻り値は timestamptz なので ::timestamp で落としています。部署は emp_dept_code を副問い合わせで引いてコードに変換しています。

status は enabled から導いています。この書き方では、enabled が渡らなかったときも ELSE に落ちて inactive で登録される点に注意してください。属性マッピングの設定漏れで enabled が解決されないと、全員が無効な状態で登録されます。未解決のときに別の値を入れたい場合は、p->>'enabled' IS NULL の場合を分けてください。

U では、すべてのカラムを COALESCE で包んでいます。更新イベントで届く attrs には変更のあった属性しか入らないため、これがないと今回渡されなかったカラムが NULL で上書きされます。status だけは COALESCE ではなく CASE で書いています。enabled は真偽値なので、false を「値なし」と区別する必要があるためです。

P はパスワード更新です。emp_cred に行があるとは限らないので ON CONFLICT ... DO UPDATE で挿入と更新を兼ねています。パスワードマッピングを設定していない場合、{attrs,password} には平文が入ります。連携先の要件にあわせて、パスワードマッピングでハッシュ化するか、プロシージャ内で pgcrypto の crypt() などを使ってください。

D では3つのテーブルから順に削除します。外部キー制約がある場合は、参照している側から先に削除します。

JSON からの値の取り出しには2つの演算子を使い分けています。p->>'opCode' はトップレベルのキーを文字列として取り出します。p#>>'{attrs,cn}' は入れ子になったキーをパスで指定します。後者は p->'attrs'->>'cn' と書いても同じです。どちらもキーが存在しなければ NULL を返します。

ユーザー取得ファンクション

取得ファンクションは問い合わせるだけなので、PL/pgSQL ではなく LANGUAGE sql で書けます。

CREATE FUNCTION ucidm_get_user(p json) RETURNS json LANGUAGE sql AS $$
  SELECT json_build_object(
           'cn',         u.display_name,
           'mail',       c.email,
           'empNo',      u.emp_no,
           'pwdChanged', to_char(u.pwd_changed, 'YYYYMMDDHH24MISS"Z"'),
           'department', d.name)
  FROM emp_user u
         LEFT JOIN emp_contact c ON c.user_name = u.user_name
         LEFT JOIN emp_dept_code d ON d.code = u.dept_code
  WHERE u.user_name = p->>'primaryID';
$$;

該当する行がなければ、この関数は NULL を返します。単一行を返す SELECT が0行だったときの結果がそのまま NULL になるので、Oracle のような例外処理は要りません。

JSON のキーは、属性マッピングの「連携先の属性名」にあわせています。名前だけでなく値の形もあわせると、連携履歴の差分が読める形になります。この例では department を emp_dept_code と結合して部署名に戻しています。カラムに入っている dept_code をそのまま返すと、変更前がコード、変更後が部署名という差分になり、比較になりません。pwdChanged を generalizedTime の書式で戻しているのも同じ理由です。

グループの実装

グループはユーザーと同じくエントリです。primaryID で識別し、属性マッピングの結果を attrs で受け取ります。ユーザーとの違いは2つあります。enabled が渡らないことと、パスワード更新の P が来ないことです。反映プロシージャが分岐するのは I、U、D の3つになります。

グループの反映プロシージャは、外部の識別子と連番の代理キーを対応づけます。RETURNING ... INTO で採番結果を受け取り、それを emp_group の主キーにします。

CREATE PROCEDURE ucidm_apply_group(p json) LANGUAGE plpgsql AS $$
DECLARE
  op  text := p->>'opCode';
  pid text := p->>'primaryID';
  gid int;
BEGIN
  IF op = 'I' THEN
    INSERT INTO emp_group_seq(primary_id) VALUES (pid) RETURNING id INTO gid;
    INSERT INTO emp_group(id, label) VALUES (gid, p#>>'{attrs,label}');
  ELSIF op = 'U' THEN
    UPDATE emp_group SET label = COALESCE(p#>>'{attrs,label}', label)
      WHERE id = (SELECT id FROM emp_group_seq WHERE primary_id = pid);
  ELSIF op = 'D' THEN
    DELETE FROM emp_member WHERE group_name = pid;
    DELETE FROM emp_group WHERE id = (SELECT id FROM emp_group_seq WHERE primary_id = pid);
    DELETE FROM emp_group_seq WHERE primary_id = pid;
  END IF;
END $$;

グループ取得ファンクションはユーザーと同じ形です。返す JSON のキーは、グループの属性マッピングの「連携先の属性名」にあわせます。

CREATE FUNCTION ucidm_get_group(p json) RETURNS json LANGUAGE sql AS $$
  SELECT json_build_object('id', s.id, 'label', g.label)
  FROM emp_group_seq s JOIN emp_group g ON g.id = s.id
  WHERE s.primary_id = p->>'primaryID';
$$;

メンバーの実装

メンバーはエントリではなく、グループとユーザーの関係です。属性を持たないため attrs も enabled も渡らず、グループとメンバーの2つの primaryID の組だけで識別します。opCode も追加の I と削除の D の2つだけで、更新に相当する操作はありません。

メンバーの追加と削除では、UCIDM はまずグループ取得ファンクションでグループの存在を確認します。グループが見つからなければエラーになり、メンバー反映プロシージャは呼ばれません。

反映プロシージャは追加と削除を分岐するだけです。UCIDM は追加前にメンバー取得ファンクションで重複を確認しますが、他の経路で登録された行と衝突しないように ON CONFLICT DO NOTHING を付けています。

CREATE PROCEDURE ucidm_apply_member(p json) LANGUAGE plpgsql AS $$
DECLARE
  op text := p->>'opCode';
BEGIN
  IF op = 'I' THEN
    INSERT INTO emp_member(group_name, user_name)
      VALUES (p->>'groupPrimaryID', p->>'memberPrimaryID') ON CONFLICT DO NOTHING;
  ELSIF op = 'D' THEN
    DELETE FROM emp_member
      WHERE group_name = p->>'groupPrimaryID' AND user_name = p->>'memberPrimaryID';
  END IF;
END $$;

メンバー取得ファンクションの戻り値は存在確認にしか使われません。行の有無さえ判別できれば、JSON の中身は何でもかまいません。追加時に JSON が返れば登録済みとしてスキップし、削除時に NULL が返れば何もしません。

CREATE FUNCTION ucidm_get_member(p json) RETURNS json LANGUAGE sql AS $$
  SELECT json_build_object('group_name', m.group_name, 'user_name', m.user_name)
  FROM emp_member m
  WHERE m.group_name = p->>'groupPrimaryID' AND m.user_name = p->>'memberPrimaryID';
$$;

psql で動作を確認する

管理画面に登録する前に、psql で作成して手で呼び出すのが確実です。UCIDM が発行するのと同じ形の SQL を実行できます。

$ psql -h localhost -U postgres -d verify

作成した6つのオブジェクトは \df で確認します。

\df ucidm_*

反映と取得を手で呼び出して、期待どおりのデータが入るかを確認します。

CALL ucidm_apply_user('{"opCode":"I","primaryID":"user01","enabled":true,
  "attrs":{"cn":"山田太郎","mail":"taro@example.com","empNo":"1042",
  "pwdChanged":"20260220213129Z","department":"営業部"}}');

SELECT ucidm_get_user('{"primaryID":"user01"}');

opCode を U、P、D に変えて、更新と削除も同じ手順で確認してください。存在しない primaryID に対して取得ファンクションが NULL を返すことも確認します。

管理画面に登録する

動作を確認できたら、CREATE 文を管理画面の対応する項目に貼り付けます。psql で使った定義から、文末のセミコロンは外しても付けたままでもかまいません。UCIDM は登録された文字列を1つの文としてそのまま実行します。

UCIDM が先に DROP するので CREATE OR REPLACE にする必要はありません。

登録後に UCIDM がインストールを実行するので、実施しない操作の設定 やドライランモードとあわせて、まず限定した範囲で連携を試すことを勧めます。ドライランモードでは反映プロシージャは呼ばれませんが、取得ファンクションは呼ばれます。

つまずきやすい点

引数の型は json 固定

前述のとおり、UCIDM の DROP 文は (json) で修飾されています。jsonb を使うと DROP が空振りします。jsonb の演算子や関数を使いたい場合は、プロシージャの内部で p::jsonb とキャストしてください。

$$ の入れ子に注意する

定義の本体は $$ で囲みます。本体の中にドル記号を2つ並べた文字列を書くと、そこで本体が終わったと解釈されます。その場合は $body$ のようにタグ付きの引用符を使ってください。

型変換の失敗は例外になる

(p#>>'{attrs,empNo}')::int は、数値として解釈できない文字列に対して例外を送出します。連携は失敗として履歴に残ります。値が不正でも連携を止めたくない場合は、CASE や正規表現で事前に判定してください。

エラーメッセージには関数名と行番号が付く

PL/pgSQL の中で発生したエラーには、PostgreSQL が PL/pgSQL function ucidm_apply_user(json) line 12 at SQL statement という位置情報を付けます。UCIDM はこれを WHERE: として、DETAIL: と HINT: とあわせてエラーメッセージに追加します。連携履歴からどの文で失敗したかを追えます。

連携先の都合で反映できないデータを検出したい場合は、RAISE EXCEPTION で自分のメッセージを返せます。

IF p#>>'{attrs,cn}' IS NULL THEN
  RAISE EXCEPTION 'cn is required for %', pid;
END IF;

プロシージャに COMMIT を書かない

UCIDM は CALL を1つの文として実行するので、プロシージャ内のすべての文が1つのトランザクションになります。例外を送出すればまとめてロールバックされます。PL/pgSQL のプロシージャは COMMIT を書けますが、この境界を崩すことになるので書かないでください。

ユーザーの追加とグループメンバーの追加は別々の呼び出しなので、別々のトランザクションになります。両者をまたぐ一貫性が必要な場合は、プロシージャの中で完結するように設計してください。

識別子は小文字に畳まれる

二重引用符で囲まない識別子は小文字として扱われます。UCIDM は小文字の名前で DROP と CALL を発行するため、通常はそのまま一致します。既存スキーマに大文字を含むテーブル名がある場合は、プロシージャの中で二重引用符付きで参照してください。