如何根据其他列值创建序列

Question

我有下面的数据表，我想为每个group_id创建一个唯一的data_id id (group_id和data_id应该是唯一的)，对于每个组，data_id应该从1开始。如何创建一个DB序列来实现这一点？或者有其他更好的方法来实现这一点吗？

group_id  data_id 
UUID-1     1  
UUID-1     2
UUID-1     3
UUID-1     4
UUID-2     1
UUID-2     2

Answer 1

在这种情况下，DB序列将不起作用。您可以使用触发器，但要注意以下几点：

试试这个：

如下所示创建触发器函数：

create or replace function trig_fun() 
returns trigger AS
$$
begin

select coalesce(max(data_id),0)+1 into new.data_id from my_table where group_id=new.group_id;

return new;

end;
$$
language plpgsql 

并在before insert事件上附加上述函数

create trigger trig_on_insert 
before insert on 
my_table
for each row
execute procedure trig_fun()

限制：

1. 如果您删除该组中具有最大值的任何组的行，则下次插入同一组时，它将重新分配相同的编号。
2. 如果您更新的任何ID大于该组中的其它ID，则下一个值将从该ID开始递增。

Answer 2

create function tgf_mytable_bi()
    returns trigger
    language plpgsql
as $f$
declare
    seq_name text;
begin
    seq_name := 'seq_'||new.group_id;
    -- Check is sequence already exists
    if (select count(*) = 0 from pg_class where relkind = 'S' and relname = seq_name) then
        -- New group detected
        -- Create new sequence for it
        execute format('create sequence %I', seq_name);
        -- Sequence exists, get next value from it
    end if;
    -- Generate next value for group
    new.data_id := nextval(seq_name);
end $f$;

create trigger trg_mytable_bi
    before insert on mytable
    for each row
    execute procedure tgf_mytable_bi();