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:
Show quoted text
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 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?
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
Import Notes
Reply to msg id not found: 028b01c512de$bff5e4d0$6e00a8c0@gt.ClickDiario.local