Re: Strange sequences - how to construct?

Here is solution using a prepopulated sequence table:

drop table if exists my_sequence cascade;
-- a new table to hold the sequence values
create table my_sequence
(
nextval integer not null, -- this is an arbitrary sequence value we want to use
key_order serial not null primary key, -- the insert order dictates the sequence bases on the key
used boolean not null default 'False' -- is the sequence spoken for
);

create index on my_sequence( used, key_order); -- index for speed
-- prepopulate the sequence with the values you need
insert into my_sequence values
(1), (1), (2), (2), (3), (3);

-- a function to generate teh next sequence and make is used

create or replace function next_sequence() returns integer as
$body$
declare
rv integer;
key_v integer;
begin
select nextval, key_order into rv , key_v from my_sequence where not used order by key_order limit 1 for update; --lock the row
update my_sequence set used = True where key_order = key_v; -- update row as used
return rv;
end;
$body$
language plpgsql;

--- example: lets get 3 sequences
select next_sequence(), next_sequence(), next_sequence();

--- inspect the table to see what happned
select * from my_sequence;

> On 10/22/2021 12:29 PM SQL Padawan <sql_padawan(at)protonmail(dot)com> wrote:
>
>
>
> Good afternoon to everybody.
>
> I wish to construct some weird sequences.
>
> 1
> 1
> 2
> 2
> &c.
>
> and with 3 ones, 4 ones... &c.
>
> Now, I know how to do a simple
> 1
> 2
> 3
> 4
>
> using both GENERATE_SERIES and using a RECURSIVE CTE.
>
> What I would like is to be able to construct my specified sequences using *_both_* GENERATE_SERIES *_and_* RECURSIVE CTEs.
>
> Regards,
>
> SQL Padawan!
>
>
>
>
>
> Sent with ProtonMail https://protonmail.com/ Secure Email.
>
>