Обсуждение: Using update statements in create function statements
CREATE OR REPLACE FUNCTION orderupdate(INTEGER, INTEGER) RETURNS INTEGER AS '
UPDATE HEXORDERS
SET ORDER_AMOUNT = (
SELECT SUM(CUSTITEM_QUANTITY * (ITEM_BASEPRICE + ((CUSTITEM_USERS - 1) * ITEM_USEPRICE)))
FROM HEXCUSTITEMS, HEXITEMS
WHERE HEXCUSTITEMS.CUSTOMER_ID = $2
AND HEXCUSTITEMS.ORDER_ID = $1
AND HEXCUSTITEMS.ITEM_ID = HEXITEMS.ITEM_ID
GROUP BY HEXCUSTITEMS.ORDER_ID, HEXCUSTITEMS.CUSTOMER_ID ),
ORDER_GST = (
SELECT SUM((CUSTITEM_QUANTITY * (ITEM_BASEPRICE + ((CUSTITEM_USERS - 1) * ITEM_USEPRICE))) * .1)
FROM HEXCUSTITEMS, HEXITEMS
WHERE HEXCUSTITEMS.CUSTOMER_ID = $2
AND HEXCUSTITEMS.ORDER_ID = $1
AND HEXCUSTITEMS.ITEM_ID = HEXITEMS.ITEM_ID
AND CUSTITEM_GST = TRUE
GROUP BY HEXCUSTITEMS.ORDER_ID, HEXCUSTITEMS.CUSTOMER_ID )
WHERE ORDER_ID = $1
AND CUSTOMER_ID = $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 for types numeric and double precision. There are no double variables in the statement - only numeric and integer. Can anyone tell me what is wrong with this syntax?
Regards
Вложения
> 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