Re: Create function statement with insert statement - Mailing list pgsql-sql
From | Susan Hoddinott |
---|---|
Subject | Re: Create function statement with insert statement |
Date | |
Msg-id | 000801c2ecf2$4484f480$1f84fea9@oemcomputer Whole thread Raw |
In response to | Re: Create function statement with insert statement (Christoph Haller <ch@rodos.fzk.de>) |
List | pgsql-sql |
Hi Chris, Pleased to (finally) report success. Here are the solutions: INSERT --------- DROP FUNCTION orderinsert() ; CREATE OR REPLACE FUNCTION orderinsert() RETURNS OPAQUE AS ' BEGIN IF NEW.CUSTOMER_ID ISNULL THEN RAISE EXCEPTION "CUSTOMER_ID cannot be NULL value" ; END IF ; IF NEW.CUSTOMER_SESSIONISNULL THEN RAISE EXCEPTION "CUSTOMER_SESSION cannot be NULL value" ; END IF ; INSERT INTO HEXORDERS ( CUSTOMER_ID, ORDER_KEY, DISTRIBUTOR_ID, ORDER_AMOUNT, ORDER_GST ) VALUES ( NEW.CUSTOMER_ID, NEW.CUSTOMER_SESSION, 1, 0, 0 ) ; RETURN NEW ; END ; ' LANGUAGE 'plpgsql' ; DROP TRIGGER HEXCUST_TRIGGER1 ON HEXCUSTOMERS; CREATE TRIGGER HEXCUST_TRIGGER1 AFTER INSERT ON HEXCUSTOMERS FOR EACH ROW EXECUTE PROCEDURE orderinsert() ; UPDATE ---------- DROP FUNCTION orderupdate() ; CREATE OR REPLACE FUNCTION orderupdate() RETURNS OPAQUE AS ' BEGIN IF NEW.ORDER_ID ISNULL THEN RAISE EXCEPTION ''ORDER_ID cannot be NULL value'' ; END IF ; IF NEW.CUSTOMER_IDISNULL THEN RAISE EXCEPTION ''CUSTOMER_ID cannot be NULL value'' ; END IF ; UPDATE HEXORDERS SET ORDER_AMOUNT = SELECT SUM(CUSTITEM_QUANTITY * (ITEM_BASEPRICE + ((CUSTITEM_USERS - 1) * ITEM_USEPRICE))) FROM HEXCUSTITEMS, HEXITEMS WHERE HEXCUSTITEMS.CUSTOMER_ID= NEW.CUSTOMER_ID AND HEXCUSTITEMS.ORDER_ID = NEW.ORDER_ID ANDHEXCUSTITEMS.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::numeric) FROM HEXCUSTITEMS, HEXITEMS WHEREHEXCUSTITEMS.CUSTOMER_ID = NEW.CUSTOMER_ID AND HEXCUSTITEMS.ORDER_ID = NEW.ORDER_ID AND HEXCUSTITEMS.ITEM_ID = HEXITEMS.ITEM_ID AND CUSTITEM_GST = TRUE GROUP BY HEXCUSTITEMS.ORDER_ID,HEXCUSTITEMS.CUSTOMER_ID ) WHERE ORDER_ID = NEW.ORDER_ID AND CUSTOMER_ID = NEW.CUSTOMER_ID ; RETURN NEW ; END ; ' LANGUAGE 'plpgsql' ; DROP TRIGGER HEXCUSTITEMS_TRIGGER1 ON HEXCUSTITEMS; CREATE TRIGGER HEXCUSTITEMS_TRIGGER1 AFTER INSERT OR UPDATE ON HEXCUSTITEMS FOR EACH ROW EXECUTE PROCEDURE orderupdate() ; DROP TRIGGER HEXCUSTITEMS_TRIGGER3 ON HEXCUSTITEMS; CREATE TRIGGER HEXCUSTITEMS_TRIGGER3 AFTER DELETE ON HEXCUSTITEMS FOR EACH ROW EXECUTE PROCEDURE orderupdate() ; I still need to test each case but it now creates without errors. Thanks for all your assistance. Regards ----- Original Message ----- From: "Christoph Haller" <ch@rodos.fzk.de> To: <pgsql-sql@postgresql.org> Cc: <susan@hexworx.com> Sent: Monday, March 17, 2003 7:25 PM Subject: Re: [SQL] Create function statement with insert statement > > > > I can select from this table although the output is slightly > different: > > > > lanname | lanispl | lanpltrusted | lanplcallfoid | lancompiler > > ----------+---------+--------------+---------------+------------- > > internal | f | f | 0 | n/a > > C | f | f | 0 | /bin/cc > > sql | f | f | 0 | postgres > > (3 rows) > > > That's ok, it changed slightly between versions. > > Did you make any progress? > > > > > Try re-writing your function in PL/pgSQL. BTW, I think I saw from your > > > other post that you don't have PL/pgSQL installed in the database you > > are using. See the createlang program or CREATE LANGUAGE statement: > > > http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/app-createlang.h tml > > > > http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/sql-createlangua ge.html > > > > I think Joe is right. Try PL/pgSQL, it's much more flexible anyway. > > Regards, Christoph > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org