From: KÖPFERL Robert <robert.koepferl@sonorys.at>
To: pgsql-sql@postgresql.org
Subject: Re: Help on Procedure running external function
Date: Mon, 4 Jul 2005 17:20:19 +0200
Message-ID: <ED4E30DD9C43D5118DFB00508BBBA76EB167CD@neptun.sonorys.at> (raw)
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
|
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: robert.koepferl@sonorys.at
Subject: Re: Help on Procedure running external function
In-Reply-To: <ED4E30DD9C43D5118DFB00508BBBA76EB167CD@neptun.sonorys.at>
* 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