X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 27FE75284D for ; Mon, 4 Jul 2005 12:20:50 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 38671-10 for ; Mon, 4 Jul 2005 15:20:39 +0000 (GMT) Received: from mx-00.sil.at (mx-00.sil.at [62.116.68.196]) by svr1.postgresql.org (Postfix) with ESMTP id 3F04252822 for ; Mon, 4 Jul 2005 12:20:36 -0300 (ADT) Received: (qmail-ldap/ctrl 21340 invoked from network); 4 Jul 2005 15:20:27 -0000 Received: from unknown (HELO io.sonorys.at) ([213.235.243.42]) (envelope-sender ) by mx-00.sil.at (qmail-ldap-1.03) with SMTP for ; 4 Jul 2005 15:20:27 -0000 Received: from neptun.sonorys.at (neptun.sonorys.at [172.24.1.17]) by io.sonorys.at (8.11.7+Sun/8.11.7) with ESMTP id j64FKRG03114 for ; Mon, 4 Jul 2005 17:20:28 +0200 (MEST) Received: by neptun.sonorys.at with Internet Mail Service (5.5.2653.19) id ; Mon, 4 Jul 2005 17:20:27 +0200 Message-ID: From: =?iso-8859-15?Q?K=D6PFERL_Robert?= To: pgsql-sql@postgresql.org Subject: Re: Help on Procedure running external function Date: Mon, 4 Jul 2005 17:20:19 +0200 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: text/plain; charset="iso-8859-15" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.008 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200507/15 X-Sequence-Number: 21993 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 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 |