How to get concecutive id values
От | Andrus Moor |
---|---|
Тема | How to get concecutive id values |
Дата | |
Msg-id | d1i06q$132u$1@news.hub.org обсуждение исходный текст |
Список | 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
В списке pgsql-general по дате отправления: