> For AI agents: the complete documentation index is available at /resource/ucidm/docs/on-premise/dev/llms.txt, the full documentation bundle is available at /resource/ucidm/docs/on-premise/dev/llms-full.txt. # PostgreSQL PL/pgSQL の開発 [UCIDM が呼び出すオブジェクト](/resource/ucidm/docs/on-premise/dev/tech/storedProcedure/index.md#ucidm-objects) を PostgreSQL の PL/pgSQL で実装する方法を説明します。`CREATE PROCEDURE` を使うため、PostgreSQL 11 以降が対象です。 呼び出しの仕様そのものは [ストアドプロシージャ／ファンクション](/resource/ucidm/docs/on-premise/dev/tech/storedProcedure/index.md) にまとめています。本ページは PostgreSQL 固有の書き方に絞ります。 ## UCIDM が発行する SQL \{#generated-sql} 実装の制約は、UCIDM が発行する SQL から決まります。PostgreSQL に対しては次の3種類を発行します。 ```sql -- 反映プロシージャの呼び出し 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つできて呼び出しが曖昧になります。 ## 接続ユーザーの権限 \{#privileges} [接続ユーザーに要る権限](/resource/ucidm/docs/on-premise/dev/tech/storedProcedure/index.md#privileges) のうち、PostgreSQL で明示的に与える必要があるのは作成の権限だけです。オブジェクトは `search_path` の先頭のスキーマに作られるので、そのスキーマへの `CREATE` 権限が要ります。削除できるのは所有者だけですが、6つのオブジェクトを作るのは接続ユーザー自身なので、所有者も接続ユーザーになります。実行の権限も、関数とプロシージャの `EXECUTE` が既定で `PUBLIC` に与えられるため、明示的な `GRANT` は要りません。 プロシージャとファンクションは既定で `SECURITY INVOKER` として動きます。読み書きするテーブルへの権限は、呼び出す接続ユーザー自身に要ります。 ```sql GRANT USAGE, CREATE ON SCHEMA app TO ucidm; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO ucidm; GRANT USAGE ON ALL SEQUENCES IN SCHEMA app TO ucidm; ``` `serial` や `identity` のカラムに値を入れる場合は、上の例のようにシーケンスへの `USAGE` も与えます。 作成先を `public` スキーマにする場合は、PostgreSQL のバージョンに注意してください。PostgreSQL 15 で `public` スキーマの `CREATE` 権限が `PUBLIC` から取り消されたため、14 以前では動いていた接続ユーザーが 15 以降ではインストールに失敗します。 ## 実装例の前提 \{#example-schema} 社員管理の既存スキーマに ID 連携する例で説明します。[Oracle PL/SQL の開発](/resource/ucidm/docs/on-premise/dev/tech/storedProcedure/oracle.md#example-schema) と同じスキーマを PostgreSQL の型で用意します。 ```sql -- ユーザーの基本情報 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` のキーになります。 | 連携先の属性名 | 連携元の属性名 | 用途 | | :----------------- | :----------------- | :--------------------------- | | `cn` | `cn` | `emp_user.display_name` | | `mail` | `mail` | `emp_contact.email` | | `empNo` | `employeeNumber` | `emp_user.emp_no` (数値) | | `pwdChanged` | `pwdChangedTime` | `emp_user.pwd_changed` (日時) | | `department` | `ou` | `emp_user.dept_code` (マスタ変換) | | `ucidmUserEnabled` | `ucidmUserEnabled` | `emp_user.status` | グループの属性マッピングは `label` に `cn` を割り当てます。 最後の `ucidmUserEnabled` は、JSON の `enabled` を解決するために必要です。動作設定で「ユーザーステータス設定情報を使用する」を off にした場合、UCIDM は「ステータス用属性名」に指定した名前を**マッピング後の属性**から探します。属性マッピングにこの行がないと `enabled` が解決されず、JSON に含まれません。「ユーザーステータス設定情報を使用する」を on にした場合はマッピングは不要です。 ## ユーザー反映プロシージャ \{#apply-user} `opCode` で4つの操作を分岐します。 ```sql 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 を返します。 ## ユーザー取得ファンクション \{#get-user} 取得ファンクションは問い合わせるだけなので、PL/pgSQL ではなく `LANGUAGE sql` で書けます。 ```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 の書式で戻しているのも同じ理由です。 ## グループの実装 \{#group} グループはユーザーと同じくエントリです。`primaryID` で識別し、属性マッピングの結果を `attrs` で受け取ります。ユーザーとの違いは2つあります。`enabled` が渡らないことと、パスワード更新の `P` が来ないことです。反映プロシージャが分岐するのは `I`、`U`、`D` の3つになります。 グループの反映プロシージャは、外部の識別子と連番の代理キーを対応づけます。`RETURNING ... INTO` で採番結果を受け取り、それを `emp_group` の主キーにします。 ```sql 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 のキーは、グループの属性マッピングの「連携先の属性名」にあわせます。 ```sql 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'; $$; ``` ## メンバーの実装 \{#member} メンバーはエントリではなく、グループとユーザーの関係です。属性を持たないため `attrs` も `enabled` も渡らず、グループとメンバーの2つの `primaryID` の組だけで識別します。`opCode` も追加の `I` と削除の `D` の2つだけで、更新に相当する操作はありません。 メンバーの追加と削除では、UCIDM はまずグループ取得ファンクションでグループの存在を確認します。グループが見つからなければエラーになり、メンバー反映プロシージャは呼ばれません。 反映プロシージャは追加と削除を分岐するだけです。UCIDM は追加前にメンバー取得ファンクションで重複を確認しますが、他の経路で登録された行と衝突しないように `ON CONFLICT DO NOTHING` を付けています。 ```sql 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 が返れば何もしません。 ```sql 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 で動作を確認する \{#verify} 管理画面に登録する前に、psql で作成して手で呼び出すのが確実です。UCIDM が発行するのと同じ形の SQL を実行できます。 ```bash $ psql -h localhost -U postgres -d verify ``` 作成した6つのオブジェクトは `\df` で確認します。 ```sql \df ucidm_* ``` 反映と取得を手で呼び出して、期待どおりのデータが入るかを確認します。 ```sql 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 を返すことも確認します。 ## 管理画面に登録する \{#register} 動作を確認できたら、CREATE 文を管理画面の対応する項目に貼り付けます。psql で使った定義から、文末のセミコロンは外しても付けたままでもかまいません。UCIDM は登録された文字列を1つの文としてそのまま実行します。 UCIDM が先に DROP するので `CREATE OR REPLACE` にする必要はありません。 登録後に UCIDM がインストールを実行するので、[実施しない操作の設定](/resource/ucidm/docs/on-premise/dev/administration/admin-ui/destination/rdbms.md#skip-operations) やドライランモードとあわせて、まず限定した範囲で連携を試すことを勧めます。ドライランモードでは反映プロシージャは呼ばれませんが、取得ファンクションは呼ばれます。 ## つまずきやすい点 \{#pitfalls} ### 引数の型は json 固定 \{#json-type} 前述のとおり、UCIDM の DROP 文は `(json)` で修飾されています。`jsonb` を使うと DROP が空振りします。`jsonb` の演算子や関数を使いたい場合は、プロシージャの内部で `p::jsonb` とキャストしてください。 ### $$ の入れ子に注意する \{#dollar-quoting} 定義の本体は `$$` で囲みます。本体の中にドル記号を2つ並べた文字列を書くと、そこで本体が終わったと解釈されます。その場合は `$body$` のようにタグ付きの引用符を使ってください。 ### 型変換の失敗は例外になる \{#cast-error} `(p#>>'{attrs,empNo}')::int` は、数値として解釈できない文字列に対して例外を送出します。連携は失敗として履歴に残ります。値が不正でも連携を止めたくない場合は、`CASE` や正規表現で事前に判定してください。 ### エラーメッセージには関数名と行番号が付く \{#error-message} PL/pgSQL の中で発生したエラーには、PostgreSQL が `PL/pgSQL function ucidm_apply_user(json) line 12 at SQL statement` という位置情報を付けます。UCIDM はこれを `WHERE:` として、`DETAIL:` と `HINT:` とあわせてエラーメッセージに追加します。連携履歴からどの文で失敗したかを追えます。 連携先の都合で反映できないデータを検出したい場合は、`RAISE EXCEPTION` で自分のメッセージを返せます。 ```sql IF p#>>'{attrs,cn}' IS NULL THEN RAISE EXCEPTION 'cn is required for %', pid; END IF; ``` ### プロシージャに COMMIT を書かない \{#transaction} UCIDM は `CALL` を1つの文として実行するので、プロシージャ内のすべての文が1つのトランザクションになります。例外を送出すればまとめてロールバックされます。PL/pgSQL のプロシージャは `COMMIT` を書けますが、この境界を崩すことになるので書かないでください。 ユーザーの追加とグループメンバーの追加は別々の呼び出しなので、別々のトランザクションになります。両者をまたぐ一貫性が必要な場合は、プロシージャの中で完結するように設計してください。 ### 識別子は小文字に畳まれる \{#identifier-case} 二重引用符で囲まない識別子は小文字として扱われます。UCIDM は小文字の名前で `DROP` と `CALL` を発行するため、通常はそのまま一致します。既存スキーマに大文字を含むテーブル名がある場合は、プロシージャの中で二重引用符付きで参照してください。