PostgreSQL自訂子序列產生：基於外鍵值的子序列建立方法
本文探討如何在PostgreSQL中基於外鍵值建立自訂子序列。 PostgreSQL的序列通常用於產生表中行的唯一標識符，但在某些情況下，您可能需要序列的產生依賴另一個列的值，從而建立子序列。
下圖展示了問題的核心：
基於自訂關聯的序列產生
考慮以下表格結構：
| 列名 | 数据类型 | 说明 |
|---|---|---|
| id | integer | 外键，指向另一张表 |
| seq | integer | 每个id的序列号 |
| data | text | 无关信息 |
目標是建立一個識別符系統，其中id和seq的每個唯一組合對應於表中的一行。
PostgreSQL 透過巧妙的表設計和觸發器組合來實現這一點。我們可以建立兩張表：things和stuff，其中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表中的行時，seq值會根據相關的id值自動設定。
結果
有了這些觸發器，就可以實現所需的序列產生。向things表插入資料會建立新的序列，向stuff表插入資料會根據id引用自動分配正確的序號。
透過結合這些技術，我們可以創建滿足特定排序要求的複雜且靈活的資料結構。
以上是如何在 PostgreSQL 中基於外鍵值建立自訂子序列？的詳細內容。更多資訊請關注PHP中文網其他相關文章！
