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...