From: Zac <zaccheob@inwind.it>
To: pgsql-sql@postgresql.org
Subject: Re: Help on Procedure running external function
Date: Mon, 04 Jul 2005 14:47:16 +0200
Message-ID: <dabb6g$7k9$1@news.hub.org> (raw)
In-Reply-To: <opsteqmfbkawcxfg@adi>
References: <opsteqmfbkawcxfg@adi>
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.htmlhttp://www.postgresql.org/docs/8.0/interactive/functions-sequence.html
Bye
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: zaccheob@inwind.it
Subject: Re: Help on Procedure running external function
In-Reply-To: <dabb6g$7k9$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox