Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1me3Xp-0005Nk-Pt for pgsql-novice@arkaria.postgresql.org; Fri, 22 Oct 2021 23:01:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1me3Xo-00026b-MR for pgsql-novice@arkaria.postgresql.org; Fri, 22 Oct 2021 23:01:48 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1me3Xo-00026S-09 for pgsql-novice@lists.postgresql.org; Fri, 22 Oct 2021 23:01:48 +0000 Received: from resqmta-po-01v.sys.comcast.net ([2001:558:fe16:19:96:114:154:160]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1me3Xl-0005Xw-E4 for pgsql-novice@lists.postgresql.org; Fri, 22 Oct 2021 23:01:47 +0000 Received: from resomta-po-14v.sys.comcast.net ([96.114.154.238]) by resqmta-po-01v.sys.comcast.net with ESMTP id e3Jmm9SKCKqmEe3XhmOskZ; Fri, 22 Oct 2021 23:01:41 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=comcast.net; s=20190202a; t=1634943701; bh=BEi/YMTo9IM82vkMlP2rcKBo87BKou1+AXzZjwFnqoU=; h=Received:Received:Date:From:To:Message-ID:Subject:MIME-Version: Content-Type; b=cPBQO+KbE4q+j70W42DCpj6Ik5kdBIosLJRix+1+Ut06jPBuTexyi/IElc6jJtHW9 +w8Tiz+2MIV+taamcL9qcS5noc1DoB/7sjUnE7yGiE5J37wGht9aUp1MWYO7KlVh+T rcI9psfg8uinbFiWGvc2vmfircNqcT2rvdFokXjdjJwIMtBYFmhsr3p1tEugEcoFX7 6JGVjZ0tOPkyj3F8ywrJM9YNWasFuArYZ3rLaMrPtON8H9dycDUxa0W1Cnpsiks66B ZVz5yvKtGXJWoTHhzLngIt+e0gswNsaxSIfdgaBLXN366f1HXlEEG2OKQO1C+TXkoZ 8HDuD4I5Txdtw== Received: from oxapp-hoa-06o.email.comcast.net ([96.116.226.145]) by resomta-po-14v.sys.comcast.net with ESMTPS id e3XNmIn0Vls4he3XhmrbGg; Fri, 22 Oct 2021 23:01:41 +0000 X-Xfinity-VAAS: gggruggvucftvghtrhhoucdtuddrgedvtddrvddvledgudeiucetufdoteggodetrfdotffvucfrrhhofhhilhgvmecuvehomhgtrghsthdqtfgvshhipdfqfgfvpdfpqffurfetoffkrfenuceurghilhhouhhtmecufedtudenucesvcftvggtihhpihgvnhhtshculddquddttddmnecujfgurhepfffhvffkjghfufggtgfrkgfoihesrgdtsggsredtjeenucfhrhhomhepvffkofcuvefjkffnffcuoehtihhmrdgthhhilhgusegtohhmtggrshhtrdhnvghtqeenucggtffrrghtthgvrhhnpeffhffhhedvfefhledutefhtdelueehhfeugedtfedtteekffduffeuvdffgfejjeenucffohhmrghinhepphhrohhtohhnmhgrihhlrdgtohhmnecukfhppeeliedrudduiedrvddviedrudeghedpvdegrdeirddvfeehrddvgeegnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehhvghlohepohigrghpphdqhhhorgdqtdeiohdrvghmrghilhdrtghomhgtrghsthdrnhgvthdpihhnvghtpeeliedrudduiedrvddviedrudeghedpmhgrihhlfhhrohhmpehtihhmrdgthhhilhgusegtohhmtggrshhtrdhnvghtpdhrtghpthhtohepshhqlhgpphgruggrfigrnhesphhrohhtohhnmhgrihhlrdgtohhmpdhrtghpthhtohepphhgshhqlhdqnhhovhhitggvsehlihhsthhsrdhpohhsthhgrhgvshhqlhdrohhrgh X-Xfinity-VMeta: sc=-100.00;st=legit Date: Fri, 22 Oct 2021 16:01:14 -0700 (PDT) From: TIM CHILD To: SQL Padawan , "pgsql-novice@lists.postgresql.org" Message-ID: <1883565870.300143.1634943674952@connect.xfinity.com> In-Reply-To: References: Subject: Re: Strange sequences - how to construct? MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_300142_1801633221.1634943674918" X-Priority: 3 Importance: Normal X-Mailer: Open-Xchange Mailer v7.10.5-Rev21 X-Originating-IP: ::ffff:24.6.235.244 X-Originating-Client: open-xchange-appsuite List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_300142_1801633221.1634943674918 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit 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 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. > > ------=_Part_300142_1801633221.1634943674918 MIME-Version: 1.0 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit
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@protonmail.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 Secure Email.

------=_Part_300142_1801633221.1634943674918--