How to get concecutive id values

From: "Andrus Moor" <eetasoft(at)online(dot)ee>
To: pgsql-general(at)postgresql(dot)org
Subject: How to get concecutive id values
Date: 2005-03-19 19:55:22
Message-ID: d1i06q$132u$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message perico 2005-03-19 19:59:38 Betr: Re: Question insert data
Previous Message Marcin Piotr Grondecki 2005-03-19 18:46:09 Re: Question insert data