基于另一列的 PostgreSQL 序列 [英] PostgreSQL sequence based on another column

问题描述

假设我有一张这样的桌子:

Lets say I have a table as such:

Column   |     Type    |                        Notes
---------+------------ +----------------------------------------------------------
 id      | integer     | An ID that's FK to some other table
 seq     | integer     | Each ID gets its own seq number
 data    | text        | Just some text, totally irrelevant.

id + seq 是组合键.

我想看到的是:

ID  | SEQ   |                        DATA
----+------ +----------------------------------------------
 1  | 1     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 1  | 2     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 1  | 3     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 1  | 4     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 2  | 1     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 3  | 1     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 3  | 2     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 3  | 3     | Quick brown fox, lorem ipsum, lazy dog, etc etc.
 3  | 4     | Quick brown fox, lorem ipsum, lazy dog, etc etc.

如您所见，id 和 seq 的组合是唯一的.

As you can see, a combination of id and seq is unique.

我不确定如何设置我的表(或插入语句?)来执行此操作.我想插入 id 和 data，导致 seq 成为依赖于 id 的子序列.

I'm not sure how to set up my table (or insert statement?) to do this. I'd like to insert id and data, resulting in seq being a sub-sequence dependent on id.

推荐答案

没问题！我们将制作两个表，things 和 stuff.stuff 将是您在问题中描述的表格，而 things 是它所指的表格:

No problem! We're going to make two tables, things and stuff. stuff will be the table you describe in your question, and things is the one it refers to:

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)
);

然后我们将使用一个触发器设置things，该触发器将在每次创建行时创建一个新序列:

Then we'll set things up with a trigger that will create a new sequence every time a row is created:

CREATE FUNCTION make_thing_seq() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
  execute format('create sequence thing_seq_%s', NEW.id);
  return NEW;
end
$$;

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

现在我们将得到 thing_seq_1、thing_seq_2 等，等等......

Now we'll end up with thing_seq_1, thing_seq_2, etc, etc...

现在在 stuff 上触发另一个触发器，以便它每次都使用正确的序列:

Now another trigger on stuff so that it uses the right sequence each time:

CREATE FUNCTION fill_in_stuff_seq() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
  NEW.seq := nextval('thing_seq_' || NEW.id);
  RETURN NEW;
end
$$;

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

这将确保当行进入 stuff 时，id 列用于找到正确的序列来调用 nextval.

That'll ensure that when rows go into stuff, the id column is used to find the right sequence to call nextval on.

这是一个演示:

test=# insert into things (name) values ('Joe');
INSERT 0 1
test=# insert into things (name) values ('Bob');
INSERT 0 1
test=# select * from things;
 id | name
----+------
  1 | Joe
  2 | Bob
(2 rows)

test=# d
              List of relations
 Schema |     Name      |   Type   |  Owner
--------+---------------+----------+----------
 public | stuff         | table    | jkominek
 public | thing_seq_1   | sequence | jkominek
 public | thing_seq_2   | sequence | jkominek
 public | things        | table    | jkominek
 public | things_id_seq | sequence | jkominek
(5 rows)

test=# insert into stuff (id, notes) values (1, 'Keychain');
INSERT 0 1
test=# insert into stuff (id, notes) values (1, 'Pet goat');
INSERT 0 1
test=# insert into stuff (id, notes) values (2, 'Family photo');
INSERT 0 1
test=# insert into stuff (id, notes) values (1, 'Redundant lawnmower');
INSERT 0 1
test=# select * from stuff;
 id | seq |        notes
----+-----+---------------------
  1 |   1 | Keychain
  1 |   2 | Pet goat
  2 |   1 | Family photo
  1 |   3 | Redundant lawnmower
(4 rows)

test=#

这篇关于基于另一列的 PostgreSQL 序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！