はじめに
あるorganizationに紐づくusersテーブルをリストパーティションで作成したいなと思い、そういうAPIを作成しようかなと思っていたところpostgreSQLのFUNCTION機能を使えば、動的に作成できそうだと知ったので方法をまとめます。
前提として作ろうとしていたAPIは以下のようなものです。
- organizationのレコードの挿入
- 1で作成したorganizationのidでパーティション化されたusersテーブルを作成
ChatGPTに以上の仕様でAPI作成頼んだところ以下のようにSQLだけで対応できるようなコードが返ってきました。
恥ずかしながらFUNCTION知らなかったので、そもそも何をしているかメモしておきます。
他にも色々な仕様があるので、詳細はドキュメントを参照してください。
PostgreSQL 15.4文書 CREATE FUNCTION
そもそもこの記法はSQLを拡張したPL/pgSQLという言語で書かれており、複雑な処理をデータベースサーバ側で行うことができます。この複雑な処理（一般的なSQLで実行できない処理）をストアドプロシージャと呼びます。
CREATE OR REPLACE FUNCTION create_users_table()
RETURNS TRIGGER AS $$
BEGIN
EXECUTE FORMAT('CREATE TABLE users_organization_%s (
CHECK (organization_id = %s),
LIKE users INCLUDING ALL
)', NEW.id, NEW.id);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER create_users_table_trigger
AFTER INSERT ON organizations
FOR EACH ROW
EXECUTE FUNCTION create_users_table();
FUNCTION
CREATE OR REPLACE で関数の作成 or 置き換えをしています。
ここではcreate_users_tableという名前の関数を作成しています。（引数等も取ることができます）
RETURNSで返り値の設定をします。
何かのトリガーで使いたい場合には、RETURNS TRIGGERとするのがお決まりのようです。
TRIGGERについては次項で説明します。
RETURNS TRIGGER AS $$の$$に関してはストアドプロシージャの起点、つまりコードブロックの始まりです。
最後の方にも$$がありますが、そちらは終点です。1行であればシングルクオートで置き換えられるようです。
最後のLANGUAGE plpgsqlですが、これはPL/pgSQLで記述されてることを示しているのかと思います。他にもplpythonu等といった拡張機能があるようです。
BEGIN
ここで実行される関数の処理の中身を書きます。
TRIGGER
以下のドキュメントより引用します。
作成したトリガは指定したテーブル、ビューまたは外部テーブルと関連付けられ、そのテーブルに特定の操作が行われた時に指定した関数function_nameを実行します。
PostgreSQL 15.4文書 CREATE TRIGGER
何かの操作を起点に他の操作を行う関数的なものです。
今回は、organizationsテーブルに新しいorganizationがinsertされたら、挿入されたそれぞれのorganizationに対してcreate_users_table()を実行するようにしています。（FOR EACH ROWでそれを定義）
まとめ
DB側で処理を入れると色々楽そうですね。管理が大変そうなので、まずは簡単なものを導入できればと思います。