Hello,

I am trying to create a database trigger= =20 which updates a second table. I have created the following funct= ion=20 in accordance with the reference manual documentation (7.2). <= /DIV>

CREATE OR REPLACE FUNCTION orderupdate(INTEGER, INTEGER) RETURNS INTEGER= AS=20 '

UPDATE HEXORDERS

SET ORDER_AMOUNT =3D (

SELECT SUM(CUSTITEM_QUANTITY * (ITEM_BASEPRICE + ((CUSTITEM_USERS - 1) *= =20 ITEM_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 (

SELECT SUM((CUSTITEM_QUANTITY * (ITEM_BASEPRICE + ((CUSTITEM_USERS - 1) = *=20 ITEM_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 "*"=20 operator for types numeric and double precision. There are no double= =20 variables in the statement - only numeric and integer. Can anyo= ne=20 tell me what is wrong with this syntax?

Regards

= ------=_NextPart_001_0039_01C2E8BA.66768680-- ------=_NextPart_000_0038_01C2E8BA.66768680 Content-Type: application/octet-stream; name="Susan Hoddinott.vcf" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="Susan Hoddinott.vcf" BEGIN:VCARD VERSION:2.1 N:Hoddinott;Susan FN:Susan Hoddinott EMAIL;INTERNET:susan@perth.dialix.com.au EMAIL;PREF;INTERNET:susan@hexworx.com REV:20030312T091119Z END:VCARD ------=_NextPart_000_0038_01C2E8BA.66768680-- From ch@rodos.fzk.de Thu Mar 13 09:02:31 2003 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