Re: Help on Procedure running external function

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