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

Answer 1

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