plpgsql

PL/pgSQL の基礎知識

詳細な知識：包括的なドキュメントを参照するには、テクノロジーに postgresql を指定して mcp__documentation__fetch_docs を使用してください。

基本構造

CREATE OR REPLACE FUNCTION function_name(param1 type, param2 type)
RETURNS return_type
LANGUAGE plpgsql
AS $$
DECLARE
    -- Variable declarations
    var1 type;
    var2 type := default_value;
BEGIN
    -- Function body
    RETURN result;
END;
$$;

関数

基本的な関数

CREATE OR REPLACE FUNCTION get_user_name(user_id INT)
RETURNS VARCHAR
LANGUAGE plpgsql
AS $$
DECLARE
    user_name VARCHAR;
BEGIN
    SELECT name INTO user_name
    FROM users
    WHERE id = user_id;

    RETURN user_name;
END;
$$;

-- Usage
SELECT get_user_name(1);

複数の戻り値を持つ関数

CREATE OR REPLACE FUNCTION get_user_info(p_user_id INT)
RETURNS TABLE(name VARCHAR, email VARCHAR, order_count BIGINT)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT u.name, u.email, COUNT(o.id)::BIGINT
    FROM users u
    LEFT JOIN orders o ON o.user_id = u.id
    WHERE u.id = p_user_id
    GROUP BY u.id;
END;
$$;

-- Usage
SELECT * FROM get_user_info(1);

OUT パラメーターを持つ関数

CREATE OR REPLACE FUNCTION calculate_stats(
    IN p_user_id INT,
    OUT total_orders INT,
    OUT total_amount NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT COUNT(*), COALESCE(SUM(total), 0)
    INTO total_orders, total_amount
    FROM orders
    WHERE user_id = p_user_id;
END;
$$;

-- Usage
SELECT * FROM calculate_stats(1);

SETOF 関数（複数行）

CREATE OR REPLACE FUNCTION get_active_users()
RETURNS SETOF users
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY SELECT * FROM users WHERE status = 'active';
END;
$$;

-- Usage
SELECT * FROM get_active_users();

プロシージャ（PostgreSQL 11 以降）

CREATE OR REPLACE PROCEDURE transfer_funds(
    sender_id INT,
    receiver_id INT,
    amount NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Deduct from sender
    UPDATE accounts SET balance = balance - amount WHERE id = sender_id;

    -- Add to receiver
    UPDATE accounts SET balance = balance + amount WHERE id = receiver_id;

    -- Commit transaction
    COMMIT;
END;
$$;

-- Usage
CALL transfer_funds(1, 2, 100.00);

変数と型

DECLARE
    -- Scalar types
    v_count INT := 0;
    v_name VARCHAR(100);
    v_amount NUMERIC(10,2) DEFAULT 0.00;
    v_active BOOLEAN := TRUE;
    v_created TIMESTAMP := NOW();

    -- Type from column
    v_email users.email%TYPE;

    -- Type from row
    v_user users%ROWTYPE;

    -- Record (dynamic)
    v_record RECORD;

    -- Array
    v_ids INT[] := ARRAY[1, 2, 3];

    -- Constant
    c_tax_rate CONSTANT NUMERIC := 0.21;
BEGIN
    -- ...
END;

制御構造

IF 文

IF condition THEN
    -- statements
ELSIF another_condition THEN
    -- statements
ELSE
    -- statements
END IF;

-- Example
IF v_count > 100 THEN
    v_status := 'high';
ELSIF v_count > 50 THEN
    v_status := 'medium';
ELSE
    v_status := 'low';
END IF;

CASE 文

CASE expression
    WHEN value1 THEN
        -- statements
    WHEN value2 THEN
        -- statements
    ELSE
        -- statements
END CASE;

-- Searched CASE
CASE
    WHEN condition1 THEN
        -- statements
    WHEN condition2 THEN
        -- statements
    ELSE
        -- statements
END CASE;

ループ

-- Simple loop
LOOP
    -- statements
    EXIT WHEN condition;
END LOOP;

-- WHILE loop
WHILE condition LOOP
    -- statements
END LOOP;

-- FOR loop (integer range)
FOR i IN 1..10 LOOP
    RAISE NOTICE 'i = %', i;
END LOOP;

-- FOR loop (reverse)
FOR i IN REVERSE 10..1 LOOP
    -- statements
END LOOP;

-- FOR loop (query result)
FOR v_record IN SELECT * FROM users WHERE status = 'active' LOOP
    RAISE NOTICE 'User: %', v_record.name;
END LOOP;

-- FOREACH (arrays)
FOREACH v_id IN ARRAY v_ids LOOP
    RAISE NOTICE 'ID: %', v_id;
END LOOP;

例外処理

BEGIN
    -- Statements that might fail
    INSERT INTO users (email) VALUES (p_email);
EXCEPTION
    WHEN unique_violation THEN
        RAISE NOTICE 'Email already exists: %', p_email;
        RETURN NULL;
    WHEN not_null_violation THEN
        RAISE EXCEPTION 'Email cannot be null';
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Unexpected error: % %', SQLERRM, SQLSTATE;
END;

よく使われる例外コード

例外	説明
unique_violation	キーの重複
not_null_violation	NOT NULL 列に NULL
foreign_key_violation	FK 制約違反
check_violation	CHECK 制約違反
division_by_zero	ゼロ除算
no_data_found	SELECT INTO が行を返さなかった
too_many_rows	SELECT INTO が複数行を返した

例外の発生

-- Notice (info)
RAISE NOTICE 'Processing user %', v_user_id;

-- Warning
RAISE WARNING 'Value seems too high: %', v_amount;

-- Exception (stops execution)
RAISE EXCEPTION 'Invalid user ID: %', v_user_id;

-- With error code
RAISE EXCEPTION 'Invalid input' USING ERRCODE = 'invalid_parameter_value';

トリガー

基本的なトリガー

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at := NOW();
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_users_update
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

トリガー変数

変数	説明
NEW	新しい行（INSERT／UPDATE）
OLD	古い行（UPDATE／DELETE）
TG_OP	操作：INSERT、UPDATE、DELETE
TG_TABLE_NAME	テーブル名
TG_WHEN	BEFORE または AFTER

監査トリガー

CREATE OR REPLACE FUNCTION audit_changes()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, operation, new_data)
        VALUES (TG_TABLE_NAME, 'INSERT', row_to_json(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, operation, old_data, new_data)
        VALUES (TG_TABLE_NAME, 'UPDATE', row_to_json(OLD), row_to_json(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, operation, old_data)
        VALUES (TG_TABLE_NAME, 'DELETE', row_to_json(OLD));
        RETURN OLD;
    END IF;
END;
$$;

CREATE TRIGGER trg_users_audit
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW
    EXECUTE FUNCTION audit_changes();

条件付きトリガー

CREATE TRIGGER trg_orders_notify
    AFTER INSERT ON orders
    FOR EACH ROW
    WHEN (NEW.total > 1000)
    EXECUTE FUNCTION notify_high_value_order();

動的 SQL

CREATE OR REPLACE FUNCTION search_table(
    p_table TEXT,
    p_column TEXT,
    p_value TEXT
)
RETURNS SETOF RECORD
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY EXECUTE format(
        'SELECT * FROM %I WHERE %I = $1',
        p_table, p_column
    ) USING p_value;
END;
$$;

-- With EXECUTE INTO
DECLARE
    v_count INT;
BEGIN
    EXECUTE 'SELECT COUNT(*) FROM ' || quote_ident(p_table)
    INTO v_count;
END;

カーソル

CREATE OR REPLACE FUNCTION process_orders()
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
    v_cursor CURSOR FOR SELECT * FROM orders WHERE status = 'pending';
    v_order orders%ROWTYPE;
BEGIN
    OPEN v_cursor;
    LOOP
        FETCH v_cursor INTO v_order;
        EXIT WHEN NOT FOUND;

        -- Process order
        UPDATE orders SET status = 'processing' WHERE id = v_order.id;
    END LOOP;
    CLOSE v_cursor;
END;
$$;

-- FOR loop cursor (auto open/close)
FOR v_order IN SELECT * FROM orders WHERE status = 'pending' LOOP
    -- Process
END LOOP;

ベストプラクティス

実施すること

• 型の安全性を確保するために %TYPE と %ROWTYPE を使用します
• 1 行だけが返されることを期待する SELECT INTO では STRICT を使用します
• 動的 SQL の識別子には %I を指定して format() を使用します
• エラー処理には例外ブロックを使用します
• 複数行には RETURNS SETOF または RETURNS TABLE を使用します

避けること

• 動的 SQL に文字列連結を使用する（SQL インジェクションのリスクがあります）
• 例外を無視する
• セットベースの操作で処理できる場合にカーソルを使用する
• 明確な命名なしで副作用のある関数を作成する

このスキルを使用しない場合

• 基本的な PostgreSQL SQL - クエリ、インデックス、データ型には postgresql スキルを使用してください
• PL/SQL（Oracle） - Oracle のプロシージャには plsql スキルを使用してください
• T-SQL（SQL Server） - SQL Server のプロシージャには tsql スキルを使用してください
• 基本的な SQL - ANSI SQL の基本には sql-fundamentals を使用してください

アンチパターン

アンチパターン	問題	解決策
サニタイズせずに動的 SQL を使用する	SQL インジェクション	quote_ident／quote_literal または format() を使用する
例外を処理しない	サイレントな失敗	EXCEPTION ブロックを追加する
ループで明示的なカーソルを使用する	コードが遅くなる	FOR...IN ループを使用する
FOUND 変数を無視する	ロジックエラー	クエリの後に FOUND を確認する
%TYPE／%ROWTYPE を使用しない	型の不一致	列／行の型を使用する
STRICT なしで SELECT INTO を使用する	予期しない NULL	STRICT を追加するか FOUND を確認する

簡単なトラブルシューティング

問題	診断	修正
関数が予期せず NULL を返す	FOUND 変数を確認する	NOT FOUND の処理を追加する
"query returned more than one row"	SELECT INTO が複数行を返した	LIMIT 1 を追加するか FOR ループを使用する
"column does not exist"	大文字と小文字の区別	識別子を二重引用符で囲む
トリガーが実行されない	トリガーの状態を確認する	ALTER TRIGGER ... ENABLE
パフォーマンスの問題	クエリに対して EXPLAIN ANALYZE を実行する	SQL を最適化し、インデックスを追加する

リファレンスドキュメント

• プロシージャ
• 関数
• トリガー