SQL Academy · レッスン

PL/pgSQL関数の基礎

パラメーター、RETURNS TABLE、制御フロー（IF、LOOP、FOREACH）、例外処理を備えたPL/pgSQL関数を作成します。

「PL/pgSQL関数の基礎」はCoddyKit上の無料SQL Academyレッスンです。これはレッスン2/4です。下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。これはSQL Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Academyコースには全4レッスンが含まれています。

PL/pgSQLとは

PostgreSQLに組み込まれた手続き型言語です。SQLを拡張し、変数、制御フロー、例外、クエリを動的に呼び出す機能を提供します。ストアドプロシージャやトリガー関数に使われます。

関数の基本形

単純な関数です。

CREATE OR REPLACE FUNCTION add(a INT, b INT)
RETURNS INT AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

SELECT add(2, 3);   -- 5

変数

DECLARE で宣言し、:= または INTO で代入します。

CREATE FUNCTION user_orders(uid BIGINT)
RETURNS INT AS $$
DECLARE
  cnt INT;
BEGIN
  SELECT COUNT(*) INTO cnt FROM orders WHERE user_id = uid;
  RETURN cnt;
END;
$$ LANGUAGE plpgsql STABLE;

制御フロー

IF / ELSE、FOR、WHILE、CASE を使います。

IF cnt = 0 THEN
  RAISE NOTICE 'no orders';
ELSIF cnt < 10 THEN
  RAISE NOTICE 'few';
ELSE
  RAISE NOTICE 'many';
END IF;

FOR i IN 1..10 LOOP
  RAISE NOTICE '%', i;
END LOOP;

クエリ結果を反復処理する

カーソル形式の反復処理です。

CREATE FUNCTION recalc_totals() RETURNS VOID AS $$
DECLARE
  r RECORD;
BEGIN
  FOR r IN SELECT id FROM users LOOP
    UPDATE users SET
      order_count = (SELECT COUNT(*) FROM orders WHERE user_id = r.id)
    WHERE id = r.id;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

RETURNS TABLE

結果セットを返します。

CREATE FUNCTION top_buyers(n INT)
RETURNS TABLE (user_id BIGINT, total NUMERIC) AS $$
BEGIN
  RETURN QUERY
    SELECT o.user_id, SUM(o.total)
    FROM orders o
    GROUP BY o.user_id
    ORDER BY SUM(o.total) DESC
    LIMIT n;
END;
$$ LANGUAGE plpgsql STABLE;

SELECT * FROM top_buyers(10);

IN / OUT / INOUTパラメーター

複数の戻り値を返します。

CREATE FUNCTION stats(uid BIGINT,
  OUT total NUMERIC,
  OUT cnt INT) AS $$
BEGIN
  SELECT SUM(total), COUNT(*) INTO total, cnt
  FROM orders WHERE user_id = uid;
END;
$$ LANGUAGE plpgsql STABLE;

SELECT * FROM stats(42);   -- total | cnt

例外処理

BEGIN ... EXCEPTION WHEN ... THEN を使います。

BEGIN
  INSERT INTO users (email) VALUES ($1);
EXCEPTION
  WHEN unique_violation THEN
    RAISE NOTICE 'email already exists';
  WHEN OTHERS THEN
    RAISE;
END;

ログ出力にRAISEを使う

レベルには DEBUG、LOG、INFO、NOTICE、WARNING、EXCEPTION があります。

RAISE NOTICE 'processed % rows', cnt;
RAISE EXCEPTION 'bad state: %', state USING ERRCODE = 'check_violation';

ボラティリティマーカー

プランナーが正しく扱えるよう、関数に適切なマーカーを付けます。

IMMUTABLE — 同じ引数なら常に同じ結果になる（I/Oなし）。インライン化やインデックスでの利用が可能
STABLE — 同じ引数なら、1つのクエリ内では同じ結果になる
VOLATILE — 呼び出しごとに結果が異なる可能性がある（デフォルト。NOW() や RANDOM() に適切）

SECURITY DEFINER

関数所有者の権限で実行されるため、慎重に使用してください。

CREATE FUNCTION sensitive_op() RETURNS VOID
LANGUAGE plpgsql
SECURITY DEFINER
AS $$ ... $$;

-- Always set search_path explicitly to avoid hijacking:
ALTER FUNCTION sensitive_op() SET search_path = public, pg_temp;

まとめ

PL/pgSQL はループ、IF、例外、構造化された戻り値を SQL に追加します。データの近くで実行すると効果的な、まとまりのある複数ステップのロジックに使用してください。

クイックチェック

同じ引数に対して常に同じ結果を返し、副作用や I/O がない関数には、どの volatility マーカーを使用すべきでしょうか。

よくある質問

「PL/pgSQL関数の基礎」レッスンは無料ですか？

はい。「PL/pgSQL関数の基礎」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Academyコースには全4レッスンが含まれています。

「PL/pgSQL関数の基礎」で何を学びますか？

パラメーター、RETURNS TABLE、制御フロー（IF、LOOP、FOREACH）、例外処理を備えたPL/pgSQL関数を作成します。ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Academyを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。これはレッスン2/4です。

「PL/pgSQL関数の基礎」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSQL Academyレッスンでコードを書いて実行できますか？

はい。すべてのSQL Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

← SQL Academyに戻る