X-Original-To: pgsql-sql@postgresql.org Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52]) by postgresql.org (Postfix) with SMTP id AA7EA4758E6 for ; Thu, 13 Mar 2003 04:02:31 -0500 (EST) Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ; Thu Mar 13 10:02:37 2003 +0100 Received: œby rodos.fzk.de with ESMTP (8.8.6 (PHNE_17135)/8.7.1) id KAA01072; Thu, 13 Mar 2003 10:02:31 +0100 (MET) Message-ID: <3E704926.5142D27C@rodos.fzk.de> Date: Thu, 13 Mar 2003 10:02:31 +0100 From: Christoph Haller X-Mailer: Mozilla 4.77 [en] (X11; U; HP-UX B.10.20 9000/780) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Cc: susan@hexworx.com Subject: Re: Using update statements in create function statements Content-Type: text/plain; charset=iso-8859-2 Content-Transfer-Encoding: 7bit X-Archive-Number: 200303/328 X-Sequence-Number: 12367 > I am trying to create a database trigger which updates a second table. I h= > ave created the following function in accordance with the reference manual = > documentation (7.2).=20=20 > > > CREATE OR REPLACE FUNCTION orderupdate(INTEGER, INTEGER) RETURNS INTEGER AS= > ' > > UPDATE HEXORDERS=20 > > SET ORDER_AMOUNT =3D (=20 > > SELECT SUM(CUSTITEM_QUANTITY * (ITEM_BASEPRICE + ((CUSTITEM_USERS - 1) * IT= > EM_USEPRICE))) > > FROM HEXCUSTITEMS, HEXITEMS > > WHERE HEXCUSTITEMS.CUSTOMER_ID =3D $2 > > AND HEXCUSTITEMS.ORDER_ID =3D $1 > > AND HEXCUSTITEMS.ITEM_ID =3D HEXITEMS.ITEM_ID > > GROUP BY HEXCUSTITEMS.ORDER_ID, HEXCUSTITEMS.CUSTOMER_ID ), > > ORDER_GST =3D (=20 > > SELECT SUM((CUSTITEM_QUANTITY * (ITEM_BASEPRICE + ((CUSTITEM_USERS - 1) * I= > TEM_USEPRICE))) * .1) > > FROM HEXCUSTITEMS, HEXITEMS > > WHERE HEXCUSTITEMS.CUSTOMER_ID =3D $2 > > AND HEXCUSTITEMS.ORDER_ID =3D $1 > > AND HEXCUSTITEMS.ITEM_ID =3D HEXITEMS.ITEM_ID > > AND CUSTITEM_GST =3D TRUE > > GROUP BY HEXCUSTITEMS.ORDER_ID, HEXCUSTITEMS.CUSTOMER_ID ) > > WHERE ORDER_ID =3D $1 > > AND CUSTOMER_ID =3D $2 ; > > SELECT 1 ; > > ' LANGUAGE SQL ; > > > > To be used by: > > CREATE TRIGGER HEXCUSTITEMS_TRIGGER1 > > AFTER INSERT ON HEXCUSTITEMS > > FOR EACH STATEMENT > > EXECUTE orderupdate( HEXCUSTITEMS.ORDER_ID, HEXCUSTITEMS.CUSTOMER_ID ) ; > > > > The creation of the function fails claiming that there is no "*" operator f= > or types numeric and double precision. There are no double variables in th= > e statement - only numeric and integer. Can anyone tell me what is wrong = > with this syntax? > There is one: Change > SELECT SUM((CUSTITEM_QUANTITY * (ITEM_BASEPRICE + ((CUSTITEM_USERS - 1) * I= > TEM_USEPRICE))) * .1) to > SELECT SUM((CUSTITEM_QUANTITY * (ITEM_BASEPRICE + ((CUSTITEM_USERS - 1) * I= > TEM_USEPRICE))) * .1::numeric) Regards, Christoph