Incrementing sequence with string prefix from foreign_key column

Question

This is the Cluster table:

╭────╥────────────┬─────────────╮
│ id ║  name      │   prefix    │
╞════╬════════════╪═════════════╡
│ 1  ║ Yard work  │ YA          │
│ 2  ║ Construc.. │ CR          │
└────╨────────────┴─────────────┘

Both name and prefix have uniqueness and non-null constraints.

Now we get to a material table where each row has a foreign key to a cluster.

╭────╥──────────────┬─────────────╮
│ id ║  cluster_id  │ name        │
╞════╬══════════════╪═════════════│
│ 1  ║ 1            │ Shovel      │
│ 2  ║ 1            │ Lawn mower  │
└────╨──────────────┴─────────────┘

We want to give each material another unique identifier (perhaps as the primary key even) which I can't quite figure out how to write the sequence for:

1. Prefix with a hard coded letter (W from here on)
2. Take the prefix of the cluster (YA from here on)
3. Given what we have now (W-YA) find the last value and increment by 1, this should end up being 5 characters long and padded with 0

So we would end up with, given that ^ example,

1. W-YA-00001
2. W-YA-00002
3. W-YA-00003

With another cluster_id we would then end up with

1. W-CR-00001
2. W-CR-00002

I imagine this is solved through CREATE SEQUENCE but I'm not sure where to start at all.

Note that the clusters table can receive new rows at any given moment. A row can neither change nor be deleted though.

Rows from cluster_materials can NOT be deleted, and the cluster_id can NOT be changed.

UPDATE: Sequences are not the way to go here as I need to guarantee a gapless increase in the numbers, which sequences do not provide.

UPDATE 2: Gapless Sequences for Primary Keys does describe how to achieve gapless keys, and I think it could be modified to fit my needs. However it does seem like if an insert fails here this would blow up as the count was incremented but the row is never inserted (say because it doesn't pass all constraints.) I guess that can be solved with transactions.

UPDATE 3: I'm slowly making progress in this fiddle: http://sqlfiddle.com/#!15/791ed/2

UPDATE 4: Latest progress. This is working just fine right now. It does not do any locking however and I don't know exactly how it works during concurrent inserts (which aren't an issue but locking would probably be good to prevent any unexpected issues in the future.) http://sqlfiddle.com/#!15/7ad0f/9

Answer 1

If performance is not an issue at all, then I suggest you following solution:

Schema:

create table cluster (
  id     bigint primary key,
  name   text   not null unique,
  prefix text   not null unique
);

create table material (
  id         text   primary key,
  cluster_id bigint not null references cluster,
  name       text   not null
);

Some data for cluster:

insert into cluster (id, name, prefix)
values
  (1, 'Yard work', 'YW'),
  (2, 'Construc..', 'CR');

Stored procedure that adds materials:

create or replace function add_material(
  p_cluster_id bigint,
  p_name       text
) returns text as
$body$
  -- for gapless ids
  -- prevents concurrent updates and inserts
  -- release on commit or rollback
  lock table material in exclusive mode;

  insert into material (id, cluster_id, name)
  select
    'W-' || c.prefix
         || '-'
         || lpad(
              (
                select coalesce(max(substring(m.id from '.....$')::integer) + 1, 1)
                from material m
                where m.cluster_id = c.id
              )::text,
              5,
              '0'
            ) id,
    c.id cluster_id,
    p_name as "name"
  from cluster c
  where c.id = p_cluster_id
  returning id;
$body$
  language sql volatile;

Example of usage:

select add_material(1, 'test1');

Result: W-YW-00001

select add_material(1, 'test2');

Result: W-YW-00002

select add_material(2, 'test3');

Result: W-CR-00001

To increase performance for select max(...) you could add index on material (cluster_id, substring(m.id from '.....$')::integer)