Re: Help on Procedure running external function - Mailing list pgsql-sql

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 <zaccheob@inwind.it> 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 <zaccheob@inwind.it> 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/