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