如何在 PostgreSQL 中基於外鍵值建立自訂子序列？

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中文網其他相關文章！