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...
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
