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: | Whole Thread | Raw Message | 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
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 |