Re: Help with seq numbers...

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