Creating A Character Sequence On Postgresql

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

Solution 1:

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 :

createtable test (test_id varchar);

Use a function like this one below

createor replace function next_id_test()
 returnstriggerlanguage plpgsql as $function$
beginwith cte_conform_char_list as
    (
        select val, row_number() over (orderby val), lead(val) over (orderby val)
        from (values ('A'), ('B'), ('C'), ('D'), ('E'), ('F')) as t(val) -- you can continue this list as much as you want it ;)orderby1
    )
    , 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
            leftouterjoin cte_conform_char_list cte_2
                on cte_2.row_number = cte.row_number - (selectmax(row_number) from cte_conform_char_list) +1
    )
    selectcasewhen row_number < (selectmax(row_number) from cte_built_char_list)
                then repeat(next_char, cast(rank() over (partitionby row_number orderby test_id) asint)) 
                else repeat(next_char, cast(rank() over (partitionby row_number orderby test_id) +1asint))
        endas next_test_id into new.test_id
    from test T
        innerjoin cte_built_char_list cte onsubstring(T.test_id from1for1) = cte.val
    orderbychar_length(test_id), test_id;

    returnnew;
end;
$function$;

Attach the function to a before trigger

createtrigger tg_test before inserton test foreachrowexecuteprocedure next_id_test();

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

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