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(dot)din(at)easynet(dot)ro]
|Sent: Dienstag, 05. Juli 2005 01:10
|To: Zac; pgsql-sql(at)postgresql(dot)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(at)inwind(dot)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(at)postgresql(dot)org
|