在 postgreSQL 上创建字符序列 [英] Creating a character sequence on postgreSQL

问题描述

所以，我在表列上引用了这个序列，每次我进行插入时，它的值由 nextval('ptable_pr_codigo_seq'::regclass)

定义

CREATE SEQUENCE public.ptable_pr_codigo_seq增量 1最小值 1最大值 9223372036854775807开始 103缓存 1;更改表 public.ptable_pr_codigo_seqpostgres 的所有者；

现在，我怎样才能创建一个新序列，以便每次插入时值不是数字而是 [A~ZZZ] 范围内的字符?.

<预><代码>示例:首先插入列值 = A第二 = B第三 = C第 27 名 = AA...?Th = ZZZ

解决方案

接受挑战 ;)

我认为仅使用 PostgreSQL 序列机制 (1)但是如果你真的需要这样的东西(我对你为什么需要这样的东西很感兴趣)，你可以做一个函数，返回你想要的下一个值并将其放入触发器中.

比如先创建一个表:

创建表测试(test_id varchar)；

使用下面这样的函数

创建或替换函数 next_id_test()将触发语言 plpgsql 作为 $function$ 返回开始与 cte_conform_char_list 作为(选择 val, row_number() over (order by val), Lead(val) over (order by val)from (values ('A'), ('B'), ('C'), ('D'), ('E'), ('F')) as t(val) -- 你可以继续这个尽可能多地列出;)按 1 订购), cte_built_char_list 为(选择值, cte.row_number, 合并(cte.lead, cte_2.val) 作为 next_char来自 cte_conform_char_list cte左外连接 cte_conform_char_list cte_2在 cte_2.row_number = cte.row_number - (select max(row_number) from cte_conform_char_list) +1)选择案件当 row_number <(从 cte_built_char_list 中选择 max(row_number))然后重复(next_char, cast(rank() over (partition by row_number order by test_id) as int))else repeat(next_char, cast(rank() over (partition by row_number order by test_id) + 1 as int))以 next_test_id 结束到 new.test_id从测试 T内连接 cte_built_char_list cte on substring(T.test_id from 1 for 1) = cte.val按 char_length(test_id), test_id 排序；返回新的；结尾;$函数$;

将函数附加到一个 before 触发器

在插入测试前为每一行执行过程next_id_test()创建触发器tg_test；

插入一个无关紧要的值(无论如何都会改变)

插入测试值('ttt');

然后你就可以观察到你的性格是对的.

选择 *从测试;

我知道这有点沉重，但我没有看到其他任何方式.功能可能不完美，但我没有很多时间:)

希望对你有帮助;)

So, I have this sequence referenced on a table column where everytime I do an insert its value is defined by nextval('ptable_pr_codigo_seq'::regclass)

CREATE SEQUENCE public.ptable_pr_codigo_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 103
  CACHE 1;
ALTER TABLE public.ptable_pr_codigo_seq
  OWNER TO postgres;

Now, how can I make a new sequence so everytime i do an insert the value is not a number but a character in a [A~ZZZ] range?.

Example: First insert column value = A
         Second                    = B
         Third                     = C
         27th                      = AA
         ...
         ?Th                       = ZZZ

解决方案

Challenge accepted ;)

I do not think there is any way to do that with only PostgreSQL sequence mechanism (1) But If you really need something like that (and I am quite interested on why you need something like this), you can do a function that return you the next value that you want and put it into a trigger.

For example, create a table first :

create table test (test_id varchar);

Use a function like this one below

create or replace function next_id_test()
 returns trigger language plpgsql as $function$
begin
    with cte_conform_char_list as
    (
        select val, row_number() over (order by val), lead(val) over (order by val)
        from (values ('A'), ('B'), ('C'), ('D'), ('E'), ('F')) as t(val) -- you can continue this list as much as you want it ;)
        order by 1
    )
    , cte_built_char_list as
    (
        select 
            cte.val
            , cte.row_number
            , coalesce(cte.lead, cte_2.val) as next_char
        from cte_conform_char_list cte
            left outer join cte_conform_char_list cte_2
                on cte_2.row_number = cte.row_number - (select max(row_number) from cte_conform_char_list) +1
    )
    select 
        case 
            when row_number < (select max(row_number) from cte_built_char_list)
                then repeat(next_char, cast(rank() over (partition by row_number order by test_id) as int)) 
                else repeat(next_char, cast(rank() over (partition by row_number order by test_id) + 1 as int))
        end as next_test_id into new.test_id
    from test T
        inner join cte_built_char_list cte on substring(T.test_id from 1 for 1) = cte.val
    order by char_length(test_id), test_id;

    return new;
end;
$function$;

Attach the function to a before trigger

create trigger tg_test before insert on test for each row execute procedure next_id_test();

Insert a value that doesn't really matter (it will be changed anyway)

insert into test values ('ttt');

Then you can observe you have the right character.

select *
from test;

I know that's a little heavy way but I do not see any other one. The function is probably not perfect but I do not have a lot of time :)

Hope it's going to help you ;)

这篇关于在 postgreSQL 上创建字符序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！