From: | Christoph Haller <ch(at)rodos(dot)fzk(dot)de> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Cc: | susan(at)hexworx(dot)com |
Subject: | Re: Using update statements in create function statements |
Date: | 2003-03-13 09:02:31 |
Message-ID: | 3E704926.5142D27C@rodos.fzk.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
> I am trying to create a database trigger which updates a second
table. I h=
> ave created the following function in accordance with the reference
manual =
> documentation (7.2).=20=20
>
>
> CREATE OR REPLACE FUNCTION orderupdate(INTEGER, INTEGER) RETURNS
INTEGER AS=
> '
>
> UPDATE HEXORDERS=20
>
> SET ORDER_AMOUNT =3D (=20
>
> SELECT SUM(CUSTITEM_QUANTITY * (ITEM_BASEPRICE + ((CUSTITEM_USERS - 1)
* IT=
> EM_USEPRICE)))
>
> FROM HEXCUSTITEMS, HEXITEMS
>
> WHERE HEXCUSTITEMS.CUSTOMER_ID =3D $2
>
> AND HEXCUSTITEMS.ORDER_ID =3D $1
>
> AND HEXCUSTITEMS.ITEM_ID =3D HEXITEMS.ITEM_ID
>
> GROUP BY HEXCUSTITEMS.ORDER_ID, HEXCUSTITEMS.CUSTOMER_ID ),
>
> ORDER_GST =3D (=20
>
> SELECT SUM((CUSTITEM_QUANTITY * (ITEM_BASEPRICE + ((CUSTITEM_USERS -
1) * I=
> TEM_USEPRICE))) * .1)
>
> FROM HEXCUSTITEMS, HEXITEMS
>
> WHERE HEXCUSTITEMS.CUSTOMER_ID =3D $2
>
> AND HEXCUSTITEMS.ORDER_ID =3D $1
>
> AND HEXCUSTITEMS.ITEM_ID =3D HEXITEMS.ITEM_ID
>
> AND CUSTITEM_GST =3D TRUE
>
> GROUP BY HEXCUSTITEMS.ORDER_ID, HEXCUSTITEMS.CUSTOMER_ID )
>
> WHERE ORDER_ID =3D $1
>
> AND CUSTOMER_ID =3D $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 f=
> or types numeric and double precision. There are no double variables
in th=
> e statement - only numeric and integer. Can anyone tell me what is
wrong =
> with this syntax?
>
There is one:
Change
> SELECT SUM((CUSTITEM_QUANTITY * (ITEM_BASEPRICE + ((CUSTITEM_USERS -
1) * I=
> TEM_USEPRICE))) * .1)
to
> SELECT SUM((CUSTITEM_QUANTITY * (ITEM_BASEPRICE + ((CUSTITEM_USERS -
1) * I=
> TEM_USEPRICE))) * .1::numeric)
Regards, Christoph
From | Date | Subject | |
---|---|---|---|
Next Message | Christoph Haller | 2003-03-13 09:36:27 | Re: The folding of unquoted names to lower case in PostgreSQL is |
Previous Message | Tomasz Myrta | 2003-03-13 08:57:02 | Re: nearest match |