How Can I Create Custom Sub-Sequences in PostgreSQL Based on Foreign Key Values?

PostgreSQL custom subsequence generation: subsequence creation method based on foreign key values

This article explores how to create custom subsequences based on foreign key values ​​in PostgreSQL. PostgreSQL's sequences are typically used to generate unique identifiers for rows in a table, but in some cases you may need the generation of a sequence to depend on the value of another column, thus creating a subsequence.

The following image shows the core of the problem:

Sequence generation based on custom correlation

Consider the following table structure:

列名	数据类型	说明
id	integer	外键，指向另一张表
seq	integer	每个id的序列号
data	text	无关信息

The goal is to build an identifier system where each unique combination of id and seq corresponds to a row in the table.

PostgreSQL achieves this through a clever combination of table design and triggers. We can create two tables: things and stuff, where things represents unique identifiers and stuff stores the actual data associated with each identifier.

Create tables and triggers

thingsThe table can create an id as the primary key:

CREATE TABLE things (
    id serial primary key,
    name text
);

For the stuff table, we define a combination of id and seq as the primary key:

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

Next, we implement a trigger function named things on the make_thing_seq table. This trigger is called after every row insertion and creates a new sequence with the inserted id.

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

Then, we create a trigger named things on the make_thing_seq table:

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

This ensures that whenever a new row is inserted into the things table, a corresponding sequence is created.

Finally, we create a trigger function named fill_in_stuff_seq that is called before each row insertion and populates the seq column with the appropriate next value of the sequence.

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

Create a trigger named stuff on the fill_in_stuff_seq table:

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

This ensures that when a row in the stuff table is inserted, the seq value is automatically set based on the associated id value.

Results

With these triggers, the desired sequence generation can be achieved. Inserting data into the things table will create a new sequence, and inserting data into the stuff table will automatically assign the correct sequence number based on the id reference.

By combining these techniques, we can create complex and flexible data structures that meet specific sorting requirements.

The above is the detailed content of How Can I Create Custom Sub-Sequences in PostgreSQL Based on Foreign Key Values?. For more information, please follow other related articles on the PHP Chinese website!