Custom progressive sequence (per year) with a column as prefix

Question

I need to create a custom sequence based on a specific column, added as a prefix. I know it is possible to customize the sequence as well as the nextval, but I'm not sure if it is possible to use the column of a specific table.

This is the structure of the table with the essential information:

create table tab
(
    id serial not null
        constraint tab_pkey primary key,
    year varchar(4) not null,
    seq  varchar(20) not null
);

create sequence tab_id_seq as integer;

I would like to automatically populate the "seq" column, as happens for normal sequences, according to this format:

{year}_{sequence}

where {year}_ is the prefix, while {sequence} is a progressive that starts again from 1 every year.

DESIRED RESULT
|--------|----------|---------|
|   id   |   year   |   seq   |
|--------|----------|---------|
|   10   |   2019   | 2019_1  |
|--------|----------|---------|
|   11   |   2019   | 2019_2  |
|--------|----------|---------|
|   12   |   2019   | 2019_3  |
|--------|----------|---------|
|   13   |   2019   | 2019_4  |
|--------|----------|---------|
|   14   |   2020   | 2020_1  | <--- sequence restarting
|--------|----------|---------|
|   15   |   2020   | 2020_2  |
|--------|----------|---------|
|   16   |   2020   | 2020_3  |
|--------|----------|---------|

N.B. there is no direct relationship between the id column and {sequence} element

Jaisus · Answer 1 · 2019-09-12T09:15:20.823

1

For the following test structure :

create table test 
(
    id serial primary key
    , year_val int
    , seq varchar (10)
);

create or replace function fn_test () returns trigger language plpgsql as $$
declare
    res_name varchar;
begin
    drop table if exists tmp_test;
    create temporary table tmp_test as select * from test;
    insert into tmp_test values (new.id, new.year_val);

    with cte as
    (
        select *
            , year_val::varchar||'_'||(count(*) over (partition by year_val order by id))::varchar as built_res_name
        from tmp_test
    )
    select built_res_name into res_name
    from cte
    where id = new.id;

    new.seq := res_name;
    return new;
end;
$$;

CREATE TRIGGER tg_test BEFORE INSERT ON test
    FOR EACH ROW EXECUTE FUNCTION fn_test();

insert into test (year_val)
values (2019),(2019),(2019),(2019),(2020),(2020),(2020);

edited Sep 12 '19 at 09:15

answered Sep 11 '19 at 15:14

Jaisus

1,019
5
14

Thank you for the answer. Unfortunately it is not what I am looking for, the seq column must be stored in that table during the creation of the record. – Francesco Simeoli Sep 11 '19 at 15:39
Can I ask why before going further? – Jaisus Sep 12 '19 at 08:58
I edited my post with a possible solution. It can be perfected in some ways I think but it is working. – Jaisus Sep 12 '19 at 09:17
I confess that I don't really like this approach too much, but unfortunately I have constraints that don't allow me to do otherwise. In other circumstances I would have solved the problem at the application level or by calculating the field with a select, as you initially suggested. The seq column must work in the same way as a serial column, automatically calculated during creation, without the risk of collisions. – Francesco Simeoli Sep 12 '19 at 10:48
Thank you very much for the help, your solution is very close to the result I want to achieve, however the sequence calculation is based on the current records on the database, this means that if I execute a delete on the table, a new insertion involves the collision of two values. In a normal sequence a delete or a failed transaction also involves a gap in the numbering. In the end I found a solution based on the use of multiple sequences. I try to detail the whole procedure in the answer. Thanks again! – Francesco Simeoli Sep 12 '19 at 10:49

score 1 · Accepted Answer · answered Sep 12 '19 at 11:04

In the end I found a solution by using multiple sequences (one per year), created dynamically when entering the record. A trigger, before the insertion invoke a procedure that creates the sequence (if it does not exist) and assigns the value to the seq column (if not assigned).

WORKFLOW

record insertion
sequence creation 'tab_ {year} _seq_id' if it does not exist
if the column seq is empty the value nextval is assigned (tab_ {year} _seq_id)
test insertions and deletions to verify that the column is populated in the correct way

TABLE STRUCTURE

CREATE TABLE tab (
    id serial not null constraint tab_pkey primary key,
    year varchar(4) not null,
    seq  varchar(20)
);

FUNCTION

CREATE FUNCTION tab_sequence_trigger_function() RETURNS trigger AS $$
  BEGIN
    IF NEW.seq IS NULL OR NEW.seq = '''' THEN
      EXECUTE ('CREATE SEQUENCE IF NOT EXISTS tab_' || NEW.year || '_id_seq AS INTEGER');
      NEW.seq = NEW.year || '_' || nextval('tab_' || NEW.year || '_id_seq');
    END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

TRIGGER

CREATE TRIGGER tab_sequence_trigger
  BEFORE INSERT ON tab
  FOR EACH ROW
EXECUTE PROCEDURE tab_sequence_trigger_function();

TEST

INSERT INTO tab (year) VALUES (2019);
INSERT INTO tab (year) VALUES (2019);
INSERT INTO tab (year) VALUES (2019);
INSERT INTO tab (year) VALUES (2019);
INSERT INTO tab (year) VALUES (2019);
DELETE FROM tab WHERE id=5;
INSERT INTO tab (year) VALUES (2019);
INSERT INTO tab (year) VALUES (2019);
INSERT INTO tab (year) VALUES (2020);
INSERT INTO tab (year) VALUES (2020);
INSERT INTO tab (year) VALUES (2021);
DELETE FROM tab WHERE id=8;
DELETE FROM tab WHERE id=9;
INSERT INTO tab (year) VALUES (2021);
INSERT INTO tab (year) VALUES (2020);

RESULT

SELECT * FROM tab;

----------------------
| id | year |  seq   |
----------------------
|  1 | 2019 | 2019_1 |
----------------------
|  2 | 2019 | 2019_2 |
----------------------
|  3 | 2019 | 2019_3 |
----------------------
|  4 | 2019 | 2019_4 |
----------------------
|  6 | 2019 | 2019_6 |
----------------------
|  7 | 2019 | 2019_7 |
----------------------
| 10 | 2021 | 2021_3 |
----------------------
| 11 | 2021 | 2021_4 |
----------------------
| 12 | 2020 | 2020_3 |
----------------------

Custom progressive sequence (per year) with a column as prefix

2 Answers2