Received: from smtp015.mail.yahoo.com (smtp015.mail.yahoo.com [216.136.173.59]) by postgresql.org (8.11.3/8.11.4) with SMTP id g03L3cm73141 for ; Thu, 3 Jan 2002 16:03:38 -0500 (EST) (envelope-from janwieck@yahoo.com) Received: from psc.progress.com (HELO saturn.jw.home) (192.233.92.200) by smtp.mail.vip.sc5.yahoo.com with SMTP; 3 Jan 2002 21:03:39 -0000 Received: (from wieck@localhost) by saturn.jw.home (8.11.2/8.11.2) id g03L12I16689; Thu, 3 Jan 2002 16:01:02 -0500 From: Jan Wieck Message-Id: <200201032101.g03L12I16689@saturn.jw.home> Subject: Re: pltcl function -> plpgsql In-Reply-To: from guard at "Dec 28, 2001 12:42:47 pm" To: guard Date: Thu, 3 Jan 2002 16:01:02 -0500 (EST) CC: pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL68 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200201/24 X-Sequence-Number: 6115 guard wrote: > dear all > > I have function >> > spi_exec "Update $1 set > adv_amt=adv_amt-$OLD(adv_amt)+$OLD(gadv_amt) where $2=''$OLD($2)'' " > > how to change plpgsql code > > ps. $1 a variable It would be nice to see the entire function declaration, or at least the CREATE FUNCTION part, and not just one single statement out of it's body. Second it looks alot like an AFTER UPDATE trigger to me. In that case, I would suggest changing it into a BEFORE UPDATE one and do the modification directly in the NEW.adv_amt field. The way you're doing it now add's one more dead tuple to the table. The above is usually achieved in PL/pgSQL with the EXECUTE syntax. But I would discourage from that and suggest creating separate functions per table/attribute pair, because EXECUTE (as well as spi_exec in Tcl) statements are not cacheable. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com