public inbox for [email protected]help / color / mirror / Atom feed
Strange sequences - how to construct? 6+ messages / 3 participants [nested] [flat]
* Strange sequences - how to construct? @ 2021-10-22 19:29 SQL Padawan <[email protected]> 0 siblings, 4 replies; 6+ messages in thread From: SQL Padawan @ 2021-10-22 19:29 UTC (permalink / raw) To: [email protected] <[email protected]> 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. ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Strange sequences - how to construct? @ 2021-10-22 23:01 TIM CHILD <[email protected]> parent: SQL Padawan <[email protected]> 3 siblings, 1 reply; 6+ messages in thread From: TIM CHILD @ 2021-10-22 23:01 UTC (permalink / raw) To: SQL Padawan <[email protected]>; [email protected] <[email protected]> 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 <[email protected]> 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. > > ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Strange sequences - how to construct? @ 2021-10-23 13:09 TIM CHILD <[email protected]> parent: SQL Padawan <[email protected]> 3 siblings, 0 replies; 6+ messages in thread From: TIM CHILD @ 2021-10-23 13:09 UTC (permalink / raw) To: SQL Padawan <[email protected]>; [email protected] <[email protected]> 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; 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 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; -- example: get 5 sequences 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 <[email protected]> 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. > > ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Strange sequences - how to construct? @ 2021-10-23 13:54 Alexey M Boltenkov <[email protected]> parent: SQL Padawan <[email protected]> 3 siblings, 0 replies; 6+ messages in thread From: Alexey M Boltenkov @ 2021-10-23 13:54 UTC (permalink / raw) To: SQL Padawan <[email protected]>; [email protected] <[email protected]> On 10/22/21 22:29, SQL Padawan 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. > 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; ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Strange sequences - how to construct? @ 2021-10-23 13:59 Alexey M Boltenkov <[email protected]> parent: SQL Padawan <[email protected]> 3 siblings, 0 replies; 6+ messages in thread From: Alexey M Boltenkov @ 2021-10-23 13:59 UTC (permalink / raw) To: SQL Padawan <[email protected]>; [email protected] <[email protected]> On 10/22/21 22:29, SQL Padawan 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. > 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; ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Strange sequences - how to construct? @ 2021-10-27 18:06 SQL Padawan <[email protected]> parent: TIM CHILD <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: SQL Padawan @ 2021-10-27 18:06 UTC (permalink / raw) To: TIM CHILD <[email protected]>; [email protected] <[email protected]> > --- example: lets get 3 sequences > > select next_sequence(), next_sequence(), next_sequence(); > --- inspect the table to see what happned > select * from my_sequence; Thanks for your input on this issue. SQLP Sent with ProtonMail Secure Email. ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2021-10-27 18:06 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-10-22 19:29 Strange sequences - how to construct? SQL Padawan <[email protected]> 2021-10-22 23:01 ` TIM CHILD <[email protected]> 2021-10-27 18:06 ` SQL Padawan <[email protected]> 2021-10-23 13:09 ` TIM CHILD <[email protected]> 2021-10-23 13:54 ` Alexey M Boltenkov <[email protected]> 2021-10-23 13:59 ` Alexey M Boltenkov <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox