X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5A27952892 for ; Mon, 4 Jul 2005 09:52:44 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 07365-04 for ; Mon, 4 Jul 2005 12:52:33 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 5E8F95288B for ; Mon, 4 Jul 2005 09:52:33 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.13.1/8.13.1) with ESMTP id j64CqW6S008404 for ; Mon, 4 Jul 2005 12:52:32 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.13.1/8.13.1/Submit) id j64CmHM4007821 for pgsql-sql@postgresql.org; Mon, 4 Jul 2005 12:48:17 GMT (envelope-from news) From: Zac X-Newsgroups: pgsql.sql Subject: Re: Help on Procedure running external function Date: Mon, 04 Jul 2005 14:47:16 +0200 Organization: Hub.Org Networking Services Lines: 37 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-15; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Mozilla Thunderbird 1.0 (X11/20041206) X-Accept-Language: en-us, en In-Reply-To: To: pgsql-sql@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200507/13 X-Sequence-Number: 21991 Din Adrian wrote: > Hello, > I have a 'big' problem: > I am trying to run from a procedure a function witch generate a new > document number (max from table +1 ) and after to insert a document > with this number, but the function returns me the same number each time > because the tranzaction is not finished and the inserts are not > commited and of course the next document number is the same. > ... > > for ... > loop > nr=get_me_next_number(params); > insert into table values (nr,...) > end loop > ... > > ... > error inserting in table .. primary_key nr ..... > > Is any way in making the external function to 'know' that I inserted > another row but this insert is in a tranzaction that is not finish yet > ? ar onother solution ? > > for now I 'solved' by asking for a nr once and generate myself next > number (+1) but this is not a correct solution (in this time somebody > else could insert a document with the same nr as the procedure ) > > thank you, > Adi > > I think the best solution is to use a sequence, not "select max(id) +1 from table". Look here: http://www.postgresql.org/docs/8.0/interactive/sql-createsequence.html http://www.postgresql.org/docs/8.0/interactive/functions-sequence.html Bye