PostgreSQL で外部キー値に基づいてカスタム サブシーケンスを作成するにはどうすればよいですか?

PostgreSQL カスタム サブシーケンス生成: 外部キー値に基づくサブシーケンス作成方法

この記事では、PostgreSQL で外部キー値に基づいてカスタム サブシーケンスを作成する方法について説明します。 PostgreSQL のシーケンスは通常、テーブル内の行の一意の識別子を生成するために使用されますが、場合によっては、別の列の値に依存するシーケンスの生成が必要になり、サブシーケンスを作成する必要があります。

次の画像は問題の核心を示しています:

カスタム相関に基づくシーケンス生成

次のテーブル構造を考えてみましょう:

列名	数据类型	说明
id	integer	外键，指向另一张表
seq	integer	每个id的序列号
data	text	无关信息

目標は、id と seq の一意の組み合わせがテーブル内の行に対応する識別子システムを構築することです。

PostgreSQL は、テーブル設計とトリガーを巧みに組み合わせてこれを実現します。 things と stuff という 2 つのテーブルを作成できます。things は一意の識別子を表し、stuff は各識別子に関連付けられた実際のデータを格納します。

テーブルとトリガーを作成する

thingsテーブルは主キーとして ID を作成できます:

<code class="language-sql">CREATE TABLE things (
    id serial primary key,
    name text
);</code>

stuff テーブルの場合、id と seq の組み合わせを主キーとして定義します。

<code class="language-sql">CREATE TABLE stuff (
    id integer references things,
    seq integer NOT NULL,
    notes text,
    primary key (id, seq)
);</code>

次に、things という名前のトリガー関数を make_thing_seq テーブルに実装します。このトリガーは行が挿入されるたびに呼び出され、挿入された ID を持つ新しいシーケンスを作成します。

<code class="language-sql">CREATE FUNCTION make_thing_seq() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
  execute format('create sequence thing_seq_%s', NEW.id);
  return NEW;
end
$$;</code>

次に、things テーブルに make_thing_seq という名前のトリガーを作成します。

<code class="language-sql">CREATE TRIGGER make_thing_seq AFTER INSERT ON things FOR EACH ROW EXECUTE PROCEDURE make_thing_seq();</code>

これにより、things テーブルに新しい行が挿入されるたびに、対応するシーケンスが作成されます。

最後に、fill_in_stuff_seq という名前のトリガー関数を作成します。この関数は各行の挿入前に呼び出され、seq 列にシーケンスの適切な次の値を設定します。

<code class="language-sql">CREATE FUNCTION fill_in_stuff_seq() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
  NEW.seq := nextval('thing_seq_' || NEW.id);
  RETURN NEW;
end
$$;</code>

stuff テーブルに fill_in_stuff_seq という名前のトリガーを作成します:

<code class="language-sql">CREATE TRIGGER fill_in_stuff_seq BEFORE INSERT ON stuff FOR EACH ROW EXECUTE PROCEDURE fill_in_stuff_seq();</code>

これにより、stuff テーブルに行が挿入されるときに、関連する ID 値に基づいて seq 値が自動的に設定されます。

結果

これらのトリガーを使用すると、目的のシーケンス生成を実現できます。 things テーブルにデータを挿入すると新しいシーケンスが作成され、stuff テーブルにデータを挿入すると ID 参照に基づいて正しいシーケンス番号が自動的に割り当てられます。

これらの技術を組み合わせることで、特定の並べ替え要件を満たす複雑で柔軟なデータ構造を作成できます。

以上がPostgreSQL で外部キー値に基づいてカスタム サブシーケンスを作成するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。