Обсуждение: Help with seq numbers...
Hello, thanks a lot for your help and sorry for my newbie questions...
I have the following SP:
It is indexed by iduser (a primary key)
CREATE FUNCTION store_users(name varchar, lastname varchar) RETURNS integer AS
$body$
DECLARE
userid INTEGER := nextval('this_is_a_sequence');
BEGIN
BEGIN
INSERT INTO mytable (iduser, firstname, lname) VALUES (userid, name, lastname);
EXCEPTION
WHEN UNIQUE_VIOLATION THEN
RETURN 0;
END;
RETURN userid;
END;
$body$
LANGUAGE plpgsql;
And it is working fine, but when I get a Unique_Violation (cuz there is a iduser already) the sequence still advance to the next value. There is any way to rollback or avoid holes in the sequence? I've read the manual and it says that nextval and currval could not be rolled back, that means that there is no way to avoid that trouble?
Sorry for my bad english and thanks again...
The manual is correct. There is no way to roll back a nextval. There are a variety of workarounds suggested in the archives. Take a look. One example is precalculating a large sequence and storing it in a table. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Feb 14, 2005, at 4:12 PM, Cristian Prieto wrote: > Hello, thanks a lot for your help and sorry for my newbie questions... > > I have the following SP: > It is indexed by iduser (a primary key) > > CREATE FUNCTION store_users(name varchar, lastname varchar) RETURNS > integer AS > $body$ > DECLARE > userid INTEGER := nextval('this_is_a_sequence'); > BEGIN > BEGIN > INSERT INTO mytable (iduser, firstname, lname) VALUES > (userid, name, lastname); > EXCEPTION > WHEN UNIQUE_VIOLATION THEN > RETURN 0; > END; > RETURN userid; > END; > $body$ > LANGUAGE plpgsql; > > And it is working fine, but when I get a Unique_Violation (cuz there > is a iduser already) the sequence still advance to the next value. > There is any way to rollback or avoid holes in the sequence? I've read > the manual and it says that nextval and currval could not be rolled > back, that means that there is no way to avoid that trouble? > > Sorry for my bad english and thanks again...
On Mon, Feb 14, 2005 at 15:12:56 -0600, Cristian Prieto <cristian@clickdiario.com> wrote: > > And it is working fine, but when I get a Unique_Violation (cuz there is a iduser already) the sequence still advance tothe next value. There is any way to rollback or avoid holes in the sequence? I've read the manual and it says that nextvaland currval could not be rolled back, that means that there is no way to avoid that trouble? Why do you want to do this? If you really must have consecutive values, you need to take a different approach. If you just need unique values, then don't worry about gaps.
On Mon, 14 Feb 2005 15:47:06 -0600, Cristian Prieto <cristian@clickdiario.com> wrote: > > why don't you use a serial? that way you don't have to insert it? i > > usually let postgresql take care of it, and you can use currval to > > return a value. > > That's the trouble, I need a sp that returns the user id of the last > inserted user, and 0 if the username or email (another unique index) is > already in the database... what about using a serial for userid, but checking first is the meail or username already exists? so, in your sp, do a: select count(*) from users where email=$1 or username=$2 and then avoid the insert and just return 0? or that would be to much load? javier