From: | "Susan Hoddinott" <susan(at)hexworx(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Using update statements in create function statements |
Date: | 2003-03-12 09:11:19 |
Message-ID: | 003c01c2e877$5a633ac0$1f84fea9@oemcomputer |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 | Content-Type | Size |
---|---|---|
Susan Hoddinott.vcf | application/octet-stream | 171 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2003-03-12 09:44:41 | Re: Special characters in SQL queries |
Previous Message | Susan Hoddinott | 2003-03-12 09:03:07 | Create function statement with insert statement |