Re: Strange sequences - how to construct? - Mailing list pgsql-novice

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;