How to Create PostgreSQL Sequences Dependent on Another Column Using Triggers?

PostgreSQL sequence based on another column

In PostgreSQL, you can create a sequence that depends on another column through a trigger. Consider the following table structure:

列名	数据类型	说明
id	integer	主键，外键关联其他表
seq	integer	每个ID都有其自身的seq编号
data	text	一些文本数据，与序列无关

The id seq constitutes a combined unique key.

In order to achieve the expected results, two tables need to be created: things and stuff.

Create table:

<code class="language-sql">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)
);</code>

Create sequence trigger:

Set a trigger on the things table to create a new sequence for each row of inserted data:

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

CREATE TRIGGER make_thing_seq AFTER INSERT ON things FOR EACH ROW EXECUTE PROCEDURE make_thing_seq();</code>

Fill sequence trigger:

Finally, create a trigger on the stuff table, making sure to use the correct sequence for each insert:

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

CREATE TRIGGER fill_in_stuff_seq BEFORE INSERT ON stuff FOR EACH ROW EXECUTE PROCEDURE fill_in_stuff_seq();</code>

Demo:

Data inserted into the things and stuff tables will create and use the corresponding sequences. For example:

<code class="language-sql">insert into things (name) values ('Joe');
insert into things (name) values ('Bob');

insert into stuff (id, notes) values (1, 'Keychain');
insert into stuff (id, notes) values (1, 'Pet goat');
insert into stuff (id, notes) values (2, 'Family photo');
insert into stuff (id, notes) values (1, 'Redundant lawnmower');</code>

The result will be:

<code>| id | seq | notes |
|---|---|---|
| 1 | 1 | Keychain |
| 1 | 2 | Pet goat |
| 2 | 1 | Family photo |
| 1 | 3 | Redundant lawnmower |</code>

This approach effectively creates a sequence based on the id column of the things table, ensuring a unique sequence is generated for each id.

The above is the detailed content of How to Create PostgreSQL Sequences Dependent on Another Column Using Triggers?. For more information, please follow other related articles on the PHP Chinese website!