Using update statements in create function statements - Mailing list pgsql-sql

From Susan Hoddinott
Subject Using update statements in create function statements
Date
Msg-id 003c01c2e877$5a633ac0$1f84fea9@oemcomputer
Whole thread Raw
List pgsql-sql
Hello,
 
I am trying to create a database trigger which updates a second table.  I have created the following function in accordance with the reference manual documentation (7.2). 
 
 

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

Attachment

pgsql-sql by date:

Previous
From: "Susan Hoddinott"
Date:
Subject: Create function statement with insert statement
Next
From: Rupa Schomaker
Date:
Subject: Re: Special characters in SQL queries