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 ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
