如何使用序列和觸發器在 PostgreSQL 中產生子序列？

本文詳細介紹如何使用序列和觸發器在 PostgreSQL 中產生子序列。 此場景涉及一個具有複合主鍵（id、seq）的表，需要自訂解決方案為每個 id.

建立唯一的序列

解決方案涉及建立兩個表並實現觸發器：

表格結構：

• things: 表格儲存主要實體，以id為主鍵。
• stuff: 此表儲存子序列，包含 id（外鍵引用 things）、seq（子序號）和 notes。主鍵是 id 和 seq 的組合。

觸發器與功能：

1. things 表格觸發器 (make_thing_seq): 此觸發器在 每次插入 表後 things 執行。 它為每個新的 thing_seq_{id}.id 建立一個名為

   的新序列。

2. stuff 表格觸發器 (fill_in_stuff_seq): 此觸發器在 每次插入 表之前stuff執行。它根據插入的 thing_seq_{id} 從適當的序列 (id) 中檢索下一個值，並將其指派給 seq 列。

範例實作：

以下程式碼示範了建表和觸發器的實作：

-- Create the tables
CREATE TABLE things (
    id serial primary key,
    name text
);

CREATE TABLE stuff (
    id integer references things,
    seq integer NOT NULL,
    notes text,
    primary key (id, seq)
);

-- Trigger in things table to create new sequences
CREATE OR REPLACE FUNCTION make_thing_seq() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
  EXECUTE format('CREATE SEQUENCE IF NOT EXISTS thing_seq_%s', NEW.id); -- Added IF NOT EXISTS
  RETURN NEW;
end;
$$;

CREATE TRIGGER make_thing_seq AFTER INSERT ON things FOR EACH ROW EXECUTE PROCEDURE make_thing_seq();

-- Trigger in stuff table to use correct sequence
CREATE OR REPLACE FUNCTION fill_in_stuff_seq() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
  NEW.seq := nextval(format('thing_seq_%s', NEW.id));
  RETURN NEW;
end;
$$;

CREATE TRIGGER fill_in_stuff_seq BEFORE INSERT ON stuff FOR EACH ROW EXECUTE PROCEDURE fill_in_stuff_seq();

此設定可確保將資料插入 things 和 stuff 自動為每個 id 產生唯一的子序列，從而保持複合主鍵的完整性。 IF NOT EXISTS 子句已新增至 make_thing_seq 函數中，以防止在給定 ID 的序列已存在時出現錯誤。

以上是如何使用序列和觸發器在 PostgreSQL 中產生子序列？的詳細內容。更多資訊請關注PHP中文網其他相關文章！