From: Susan Hoddinott <susan@hexworx.com>
To: Christoph Haller <ch@rodos.fzk.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: Create function statement with insert statement
Date: Tue, 18 Mar 2003 10:01:16 +0800
Message-ID: <000801c2ecf2$4484f480$1f84fea9@oemcomputer> (raw)
References: <3E75B0BB.1F631FDC@rodos.fzk.de>
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_SESSION ISNULL 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_ID ISNULL 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
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::numeric)
FROM HEXCUSTITEMS, HEXITEMS
WHERE HEXCUSTITEMS.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
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, ch@rodos.fzk.de
Subject: Re: Create function statement with insert statement
In-Reply-To: <000801c2ecf2$4484f480$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