Re: Help on Procedure running external function

Maybe you're looking for the VOLATILE attribute of a function.

For the query optimizer it depends on what type of function you have how
often it is called.

|-----Original Message-----
|From: Din Adrian [mailto:adrian.din@easynet.ro]
|Sent: Dienstag, 05. Juli 2005 01:10
|To: Zac; pgsql-sql@postgresql.org
|Subject: Re: [SQL] Help on Procedure running external function
|
|
|
|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 ?
|
|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/intera|ctive/sql-createsequence.html
|> 
|http://www.postgresql.org/docs/8.0/intera|ctive/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
|>
|
|
|
|-- 
|Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
|
|
|---------------------------(end of 
|broadcast)---------------------------
|TIP 1: subscribe and unsubscribe commands go to 
|majordomo@postgresql.org
|