pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Using update statements in create function statements
2+ messages / 2 participants
[nested] [flat]

* Using update statements in create function statements
@ 2003-03-12 09:11 Susan Hoddinott <susan@hexworx.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Susan Hoddinott @ 2003-03-12 09:11 UTC (permalink / raw)
  To: 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

Attachments:

  Susan Hoddinott.vcf (application/octet-stream, 170B) download

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Using update statements in create function statements
@ 2003-03-13 09:02 Christoph Haller <ch@rodos.fzk.de>
  0 siblings, 0 replies; 2+ messages in thread

From: Christoph Haller @ 2003-03-13 09:02 UTC (permalink / raw)
  To: pgsql-sql; +Cc: susan@hexworx.com

> 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





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2003-03-13 09:02 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-12 09:11 Using update statements in create function statements Susan Hoddinott <susan@hexworx.com>
2003-03-13 09:02 Re: Using update statements in create function statements Christoph Haller <ch@rodos.fzk.de>

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