public inbox for [email protected]
help / color / mirror / Atom feedStrange sequences - how to construct?
6+ messages / 3 participants
[nested] [flat]
* Strange sequences - how to construct?
@ 2021-10-22 19:29 SQL Padawan <[email protected]>
2021-10-22 23:01 ` Re: Strange sequences - how to construct? TIM CHILD <[email protected]>
2021-10-23 13:09 ` Re: Strange sequences - how to construct? TIM CHILD <[email protected]>
2021-10-23 13:54 ` Re: Strange sequences - how to construct? Alexey M Boltenkov <[email protected]>
2021-10-23 13:59 ` Re: Strange sequences - how to construct? Alexey M Boltenkov <[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 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 ` Re: Strange sequences - how to construct? 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-22 19:29 Strange sequences - how to construct? SQL Padawan <[email protected]>
2021-10-22 23:01 ` Re: Strange sequences - how to construct? TIM CHILD <[email protected]>
@ 2021-10-27 18:06 ` SQL Padawan <[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
* Re: Strange sequences - how to construct?
2021-10-22 19:29 Strange sequences - how to construct? SQL Padawan <[email protected]>
@ 2021-10-23 13:09 ` TIM CHILD <[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-22 19:29 Strange sequences - how to construct? SQL Padawan <[email protected]>
@ 2021-10-23 13:54 ` Alexey M Boltenkov <[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-22 19:29 Strange sequences - how to construct? SQL Padawan <[email protected]>
@ 2021-10-23 13:59 ` Alexey M Boltenkov <[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
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