sql - 来自 foreign_key 列的带有字符串前缀的递增序列

Question

这是集群表:

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

name 和prefix 都具有唯一性和非空约束。

现在我们得到一个 Material 表，其中每一行都有一个指向集群的外键。

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

我们想给每个 Material 另一个唯一标识符(甚至可能作为主键)，我不太清楚如何编写序列:

1. 带有硬编码字母的前缀(W 从这里开始)
2. 获取集群的prefix(YA 从这里开始)
3. 鉴于我们现在拥有的 (W-YA) 找到最后一个值并递增 1，这最终应该是 5 个字符长并用 0 填充

所以我们最终会得到，给定 ^ 例子，

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

使用另一个 cluster_id，我们最终会得到

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

我想这可以通过 CREATE SEQUENCE 解决，但我完全不确定从哪里开始。

请注意，集群表可以在任何给定时刻接收新行。但是，一行既不能更改也不能删除。

不能删除 cluster_materials 中的行，并且不能更改 cluster_id。

更新:序列不是解决问题的方法，因为我需要保证数字的无缝增长，而序列不提供。

更新 2: Gapless Sequences for Primary Keys确实描述了如何实现无缝键，我认为可以对其进行修改以满足我的需要。然而，如果插入失败，这似乎会随着计数的增加而爆炸，但该行永远不会插入(比如因为它没有通过所有约束。)我想这可以通过交易来解决。

更新 3: 我在这个 fiddle 上慢慢取得进展:http://sqlfiddle.com/#!15/791ed/2

更新 4:最新进展。这现在工作得很好。然而它不做任何锁定而且我不知道它在并发插入期间是如何工作的(这不是问题但锁定可能会很好地防止将来出现任何意外问题。)http://sqlfiddle.com/#!15/7ad0f/9

Answer 1

如果性能根本不是问题，那么我建议您采用以下解决方案:

架构:

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

集群的一些数据:

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

添加素材的存储过程:

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;

使用示例:

select add_material(1, 'test1');

结果:W-YW-00001

select add_material(1, 'test2');

结果:W-YW-00002

select add_material(2, 'test3');

结果:W-CR-00001

要提高 select max(...) 的性能，您可以在 material (cluster_id, substring(m.id from '.....$') 上添加索引:整数)