はじめに

ある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側で処理を入れると色々楽そうですね。管理が大変そうなので、まずは簡単なものを導入できればと思います。