PostgreSQLでストアドプロシージャ開発をするその1 ～プロシージャの作成～

はじめに

PostgreSQLでストアド開発をする機会があったので入門しておく。

環境

Windows 11 Professional
PostgreSQL 17
Docker 4.36.0 (175267)

準備

PostgreSQLを用意する。
例によって Dockerを使う。

compose.yml

services:
  db:
    image: postgres:17
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_DB=postgres
    volumes:
      - ./initdb:/docker-entrypoint-initdb.d
      - db-data:/var/lib/postgresql/data

volumes:
  db-data:

initdb/init.sql

※前作成した、todosテーブルを利用する。

-- Migrations will appear here as you chat with AI
create table todos (
  id bigint primary key generated always as identity,
  title text not null,
  description text,
  due_date date,
  is_completed boolean default false
);

-- Migration data
insert into
  todos (title, description, due_date, is_completed)
values
  (
    'Buy groceries',
    'Milk, Bread, Cheese, and Eggs',
    '2023-11-01',
    false
  ),
  (
    'Finish project report',
    'Complete the final report for the project',
    '2023-11-05',
    false
  ),
  (
    'Call plumber',
    'Fix the leaking sink in the kitchen',
    '2023-11-02',
    false
  ),
  (
    'Book flight tickets',
    'Book tickets for the upcoming vacation',
    '2023-11-10',
    false
  ),
  (
    'Schedule dentist appointment',
    'Routine check-up and cleaning',
    '2023-11-03',
    false
  ),
  (
    'Prepare presentation',
    'Prepare slides for the upcoming meeting',
    '2023-11-04',
    false
  ),
  (
    'Renew car insurance',
    'Renew the insurance policy before it expires',
    '2023-11-06',
    false
  ),
  (
    'Organize garage',
    'Clean and organize the garage space',
    '2023-11-07',
    false
  ),
  (
    'Plan birthday party',
    'Plan a surprise birthday party for a friend',
    '2023-11-08',
    false
  ),
  (
    'Read new book',
    'Start reading the new book purchased last week',
    '2023-11-09',
    false
  );

Hello, World ストアドプロシージャを作る

CREATE PROCEDURE | PostgreSQL: Documentation: 17: CREATE PROCEDURE
https://www.postgresql.org/docs/current/sql-createprocedure.html

これを参考にして作成する。

CREATE OR REPLACE PROCEDURE hello_world()
LANGUAGE plpgsql
AS $$
BEGIN
  RAISE INFO 'Hello, World';
END;  
$$;

上記を実行して、hello_worldプロシージャを登録する。

CALL hello_world();

CALLステートメントで登録したストアドプロシージャを呼び出して実行する。

結果

OK    : [localhost/postgres]: Hello, World

解説

`CREATE OR REPLACE PROCEDURE hello_world()`

CREATE OR REPLACE PROCEDURE
PostgreSQLで新しいストアドプロシージャを作成するか、既存の同名のプロシージャを置き換える。
hello_world
ストアドプロシージャの名前。呼び出す際にこの名前を使う。
()
引数リストを表す。この場合、引数はない。

`LANGUAGE plpgsql`

LANGUAGE
プロシージャの記述に使用されるプログラミング言語を指定する。
plpgsql
PostgreSQLに組み込まれている、手続き的なSQLプログラミング言語である。SQLに加えて制御構造やエラー処理が可能となる。

`AS $$`

AS
プロシージャの本体を開始することを示す。本体は文字列リテラルとして記述される。
$$
プロシージャ本体の開始と終了を囲むデリミタ。$$を使うと、内部でシングルクォート（'）をエスケープする必要はない。

`BEGIN`

BEGIN
プロシージャの実行部分の開始を示す。この中に実行するSQL文やロジックを記述する。

`RAISE INFO 'Hello, World';`

RAISE INFO
情報レベルのログメッセージを出力する。これにより、サーバーログやクライアントコンソールにメッセージを表示できる。
'Hello, World'
出力するメッセージ文字列。この場合、「Hello, World」という文字列が出力される。

`END;`

END
プロシージャ本体の終了を示す。BEGINと対応する。

`$$;`

$$
プロシージャ本体の終了デリミタ。本体の記述を終えることを示す。
;
SQL文の終了を示す。これがないと文法エラーになる。

全体の流れ

このプロシージャを作成すると、以下の操作が可能となる。

作成: プロシージャがデータベースに登録される。
実行: CALL hello_world(); を実行すると、“Hello, World” のメッセージがサーバーのログやクライアントに出力される。

A5M2で実行した例

ストアドプロシージャを登録する。
実行を「プロシージャモード」として実行する。

「ストアドプロシージャを実行しました」が表示されればOK

CALL hello_world()を実行する。

コンソールにHello,Worldが表示されていればOK

テーブルの中身をSELECTして特定のレコードのみ登録するプロシージャ

まずは、todosからtodos_copyに登録したいので、下記のSQLを実行する。

CREATE TABLE todos_copy (
  id bigint primary key generated always as identity,
  title text not null,
  description text,
  due_date date,
  is_completed boolean default false
);

変数宣言

41.3. Declarations
https://www.postgresql.org/docs/current/plpgsql-declarations.html

CREATE OR REPLACE PROCEDURE copy_odd_todos()
LANGUAGE plpgsql
AS $$
DECLARE
  todo_record todos%ROWTYPE; -- todosテーブルの行を格納する変数
BEGIN
  -- 奇数のIDを持つ行をループで処理
  FOR todo_record IN
    SELECT * FROM todos WHERE id % 2 = 1
  LOOP
    -- 選択された行を新しいテーブルに挿入
    INSERT INTO todos_copy (title, description, due_date, is_completed)
    VALUES (todo_record.title, todo_record.description, todo_record.due_date, todo_record.is_completed);
  END LOOP;
END;
$$;

実行してみる

プロシージャ実行前の todosテーブル

実行をする

できていそう。

おまけ: titleが空白だった場合に例外処理をするようにする

CREATE OR REPLACE PROCEDURE copy_odd_todos()
LANGUAGE plpgsql
AS $$
DECLARE
  todo_record todos%ROWTYPE; -- todosテーブルの行を格納する変数
BEGIN
  -- 奇数のIDを持つ行をループで処理
  FOR todo_record IN
    SELECT * FROM todos WHERE id % 2 = 1
  LOOP
    BEGIN
      -- titleが空白の場合に例外を投げる
      IF TRIM(todo_record.title) = '' THEN
        RAISE EXCEPTION 'Error: title cannot be empty or whitespace for todo with id %', todo_record.id;
      END IF;

      -- 選択された行を新しいテーブルに挿入
      INSERT INTO todos_copy (title, description, due_date, is_completed)
      VALUES (todo_record.title, todo_record.description, todo_record.due_date, todo_record.is_completed);
    EXCEPTION
      WHEN OTHERS THEN
        -- エラーメッセージをログに記録
        RAISE NOTICE 'Skipping todo with id % due to error: %', todo_record.id, SQLERRM;
        -- 必要であれば、エラー内容を別のテーブルに記録する処理を追加
    END;
  END LOOP;
END;
$$;

UPDATE todos SET title = '';

で全部空にしておいてから、プロシージャを呼ぶ。

CALL copy_odd_todos();

下記のようなエラーが出ることを確認できた。

OK    : [localhost/postgres]: Skipping todo with id 15 due to error: Error: title cannot be empty or whitespace for todo with id 15
OK    : [localhost/postgres]: Skipping todo with id 17 due to error: Error: title cannot be empty or whitespace for todo with id 17
OK    : [localhost/postgres]: Skipping todo with id 19 due to error: Error: title cannot be empty or whitespace for todo with id 19
OK    : [localhost/postgres]: Skipping todo with id 23 due to error: Error: title cannot be empty or whitespace for todo with id 23
OK    : [localhost/postgres]: Skipping todo with id 25 due to error: Error: title cannot be empty or whitespace for todo with id 25

参考

Chapter 41. PL/pgSQL — SQL Procedural Language
https://www.postgresql.org/docs/17/plpgsql.html
PL/pgSQL/ターミナルにメッセージを表示する
https://db.just4fun.biz/?PL/pgSQL/%E3%82%BF%E3%83%BC%E3%83%9F%E3%83%8A%E3%83%AB%E3%81%AB%E3%83%A1%E3%83%83%E3%82%BB%E3%83%BC%E3%82%B8%E3%82%92%E8%A1%A8%E7%A4%BA%E3%81%99%E3%82%8B
Stored Procedureについて紹介します
https://blog.techscore.com/entry/2023/07/28/080000
第43章 PL/pgSQL — SQL手続き言語 | PostgreSQL 16.4文書
https://www.postgresql.jp/document/16/html/plpgsql.html

おわりに

ストアドプロシージャについて少し勉強してみた。
INパラメータとかOUTパラメータ、INOUTパラメータとかがあまりわかっていないので、次回はこのあたりを勉強してみる。
あとはデバッグの方法についても学んでおきたい。
カーソルについても勉強したい。
ということなのでPostgreSQL 16.4文書の43章を読み込むのがよさそう。