Thread: Strange sequences - how to construct?
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 Secure Email.
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
);
(
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;
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@protonmail.com> wrote:Good afternoon to everybody.I wish to construct some weird sequences.1122&c.and with 3 ones, 4 ones... &c.Now, I know how to do a simple1234using 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 Secure Email.
Here is a way using multiple sequences:
drop sequence if exists controller_sequence;
drop sequence if exists odd_values;
drop sequence if exists even_values;
drop sequence if exists odd_values;
drop sequence if exists even_values;
create sequence if not exists controller_sequence;
create sequence if not exists odd_values start with 1;
create sequence if not exists even_values start with 1;
create sequence if not exists odd_values start with 1;
create sequence if not exists even_values start with 1;
create function next_my_sequence() returns integer as
$body$
declare
choose integer;
begin
choose = nextval('controller_sequence');
if choose % 2 equals then
return nextval('even_values');
else
return nextval('odd_values');
end if;
end;
$body$
language plpgsql;
$body$
declare
choose integer;
begin
choose = nextval('controller_sequence');
if choose % 2 equals then
return nextval('even_values');
else
return nextval('odd_values');
end if;
end;
$body$
language plpgsql;
-- example: get 5 sequences
select next_my_sequence(), next_my_sequence(), next_my_sequence(), next_my_sequence(), next_my_sequence();
select next_my_sequence(), next_my_sequence(), next_my_sequence(), next_my_sequence(), next_my_sequence();
On 10/22/2021 12:29 PM SQL Padawan <sql_padawan@protonmail.com> wrote:Good afternoon to everybody.I wish to construct some weird sequences.1122&c.and with 3 ones, 4 ones... &c.Now, I know how to do a simple1234using 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 Secure Email.
On 10/22/21 22:29, SQL Padawan wrote:
Good afternoon to everybody.I wish to construct some weird sequences.1122&c.and with 3 ones, 4 ones... &c.Now, I know how to do a simple1234using 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 Secure Email.
GENERATE_SERIES: select unnest(array[x, x]) x from generate_series(1, 5) x;
RECURSIVE CTE: with recursive x as ( select 1 x union all select x + 1 from x where x < 5) select unnest(array[x, x]) x from x;
On 10/22/21 22:29, SQL Padawan wrote:
Good afternoon to everybody.I wish to construct some weird sequences.1122&c.and with 3 ones, 4 ones... &c.Now, I know how to do a simple1234using 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 Secure Email.
Or may be you want something strange?
*_both_* GENERATE_SERIES *_and_* RECURSIVE CTEs: with recursive x as ( select generate_series(1, 5) x union all select x + 1 from x where x = x - 1) select unnest(array[x, x]) x from x;