1. the function get_me_next_number is runing from this procedure (same
trans) but it's an external one ...
2. the second solution I'am using now (using temp a table to store each
maxnrdoc value - but the temp table give me sometimes a relation with OID
##### does not exist - problem that I can only solve by using only
execute - but I don't think I can ...?! :))
here is the example:
create temp table MagMaxNrBon
(
magazie varchar(5),
MaxNrBon int8,
CONSTRAINT temp_magbon_pkey PRIMARY KEY (magazie)
);
...
select into vnrBon maxnrbon+1 from MagMaxNrBon where magazie =
dsgroup.magazie_implicita_lansare;
if vnrbon is null
then
select into vNrBon o_gen_calc_nr_doc from o_gen_calc_nr_doc('BC'
,vEmitent,dsgroup.magazie_implicita_lansare,dsMasterBon.dataBon);
insert into MagMaxNrBon values
(dsgroup.magazie_implicita_lansare,vNrBon);
else
update MagMaxNrBon set maxnrbon=vNrBon where magazie =
dsgroup.magazie_implicita_lansare;
end if;
...
and in this way vNrBon is correct one ... I will try to use oly execute on
insert,update and select on temp table MagMaxNrBon .... (o_gen_calc_nr_doc
is the "get_me_next_number" function)
thank you,
Adria Din
On Mon, 04 Jul 2005 17:27:20 +0200, Zac <[EMAIL PROTECTED]> wrote:
Din Adrian wrote:
Yes , I know what sequence is, but our procedure for generating doc
numbers is v. big and has manny (4) parameters and we did'nt use
sequence in it for this reason ....
any other advice ?
I think there is no way to have any information about non committed
transactions.
I don't know if I understand well your problem but from what I see
"get_me_next_number" function runs in a different transaction (Why? Is
it an externale procedure that make its own connection to the DB?)),
otherwise it would see the new inserted number. The better solution is
to run "get_me_next_number" in the same transaction.
Another solution (not so good but should work) is to generate by
yourself the number (as you do) and lock the table until you end to
prevent others inserting documents.
I hope this helps you.
Bye
thak you,
Adi
On Mon, 04 Jul 2005 14:47:16 +0200, Zac <[EMAIL PROTECTED]> wrote:
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
---------------------------(end of
broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
