From: Susan Hoddinott <susan@hexworx.com>
To: pgsql-sql@postgresql.org
Subject: Using update statements in create function statements
Date: Wed, 12 Mar 2003 17:11:19 +0800
Message-ID: <003c01c2e877$5a633ac0$1f84fea9@oemcomputer> (raw)
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
Attachments:
Susan Hoddinott.vcf (application/octet-stream, 170B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: susan@hexworx.com
Subject: Re: Using update statements in create function statements
In-Reply-To: <003c01c2e877$5a633ac0$1f84fea9@oemcomputer>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox