I need to allocate some number of sequential values for primary keys.
Postgres nextval() function does not allow to allocate more than one number.
So it is not possible to get a range of sequential numbers from sequence
using nextval()
To solve this, I created table containing id current values:
CREATE TABLE appids (
key_name char (2) PRIMARY KEY,
key_value numeric(7) )
In Microsoft SQL server I can use the following stored procedure to
allocate the number of ids:
-- Allocates specified number of keys.
-- Returns last value allocated
create procedure sp_NewID
@Name char(2), @NumberOfKeys NUMERIC(7)
as
set nocount on
declare @KeyValue int
set @Name = upper( @Name )
update appids
set key_value = key_value + @NumberOfKeys
,@KeyValue = key_value
from appids
where key_name = @Name
select @KeyValue
RETURN
It uses a correlated query so that the record that's read
will be locked and then updated in the same query. This eliminates the need
for a transaction.
How to convert this code to Postgres?
Or is there a better method?
Andrus
