Help with seq numbers...

From: "Cristian Prieto" <cristian(at)clickdiario(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Help with seq numbers...
Date: 2005-02-14 21:12:56
Message-ID: 023801c512d9$f7d7b7c0$6e00a8c0@gt.ClickDiario.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Thomas F.O'Connell 2005-02-14 21:32:57 Re: Help with seq numbers...
Previous Message Jeff Davis 2005-02-14 20:40:28 Re: More info about PostgreSQL 8