Postgres Pro
Downloads
Home   >   mailing lists

Thread: Help with seq numbers...

Help with seq numbers...

From
"Cristian Prieto"
Date:
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...
 

Re: Help with seq numbers...

From
Thomas F.O'Connell
Date:
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...

Re: Help with seq numbers...

From
Bruno Wolff III
Date:
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.

Re: Help with seq numbers...

From
javier wilson
Date:
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