Thread: Create function statement with insert statement
Hello,
I am trying to create a database trigger which inserts into a second table. I have created the following function in accordance with the reference manual documentation (7.2).
CREATE OR REPLACE FUNCTION orderinsert(INTEGER, VARCHAR) RETURNS INTEGER AS '
INSERT INTO HEXORDERS ( CUSTOMER_ID, ORDER_KEY, DISTRIBUTOR_ID, ORDER_AMOUNT, ORDER_GST ) VALUES ( $1, $2, 1, 0, 0 ) ;
SELECT 1 ;
' LANGUAGE SQL ;
It fails with the cryptic error "parse error at or near ;". Can anyone tell me what is wrong with this syntax?
Regards
Attachment
You need to put your aliases in: value1 alias for $1; etc. Hello, > > I am trying to create a database trigger which inserts into a second > table. I have created the following function in accordance with the > reference manual documentation (7.2). > > > CREATE OR REPLACE FUNCTION orderinsert(INTEGER, VARCHAR) RETURNS INTEGER > AS ' > > INSERT INTO HEXORDERS ( CUSTOMER_ID, ORDER_KEY, DISTRIBUTOR_ID, > ORDER_AMOUNT, ORDER_GST ) VALUES ( $1, $2, 1, 0, 0 ) ; > > SELECT 1 ; > > ' LANGUAGE SQL ; > > > > It fails with the cryptic error "parse error at or near ;". Can anyone > tell me what is wrong with this syntax? > > Regards
> > I am trying to create a database trigger which inserts into a second table.= > I have created the following function in accordance with the reference ma= > nual documentation (7.2).=20=20 > > CREATE OR REPLACE FUNCTION orderinsert(INTEGER, VARCHAR) RETURNS INTEGER AS= > ' > INSERT INTO HEXORDERS ( CUSTOMER_ID, ORDER_KEY, DISTRIBUTOR_ID, ORDER_AMOUN= > T, ORDER_GST ) VALUES ( $1, $2, 1, 0, 0 ) ; > > SELECT 1 ; > > ' LANGUAGE SQL ; > > It fails with the cryptic error "parse error at or near ;". Can anyone te= > ll me what is wrong with this syntax? > I cannot see anything wrong with this syntax, except the newline in ORDER_AMOUN\nT. But this is probably originated from your mailing tool, isn't it? Could you provide us with the CREATE TABLE HEXORDERS ( ... ); statement? Did you try the INSERT command within psql? Regards, Christoph
Hello Susan,
>
> The relevant code for creating the hexorders table (and associated
> constraints) is:
>
>
************************************************************************
> DROP TABLE HEXORDERS ;
>
> DROP SEQUENCE HEXORDERS_SEQ ;
>
> CREATE SEQUENCE HEXORDERS_SEQ START 1 ;
>
> CREATE TABLE HEXORDERS (
> ORDER_ID INTEGER DEFAULT NEXTVAL('HEXORDERS_SEQ') NOT NULL,
> CUSTOMER_ID INTEGER NOT NULL,
> ORDER_AMOUNT NUMERIC(12,2),
> ORDER_DISCOUNT_CODE CHARACTER(1),
> ORDER_KEY VARCHAR(255),
> DISTRIBUTOR_ID INTEGER,
> ORDER_GST NUMERIC(12,2),
> ORDER_SHIPPING_COST NUMERIC(12,2),
> ORDER_DATE DATE DEFAULT CURRENT_DATE,
> ORDER_VALID BOOLEAN DEFAULT 'FALSE',
> ORDER_SHIPPING_DATE DATE,
> ORDER_DELIVERY_DATETIME TIMESTAMP,
> ORDER_FREIGHT_COMPANY VARCHAR(30),
> ORDER_CLOSE_DATE DATE );
>
>
> ALTER TABLE HEXORDERS ADD CONSTRAINT HEXORDERS_CONSTRAINT_PK
> PRIMARY KEY ( ORDER_ID );
>
>
> ALTER TABLE HEXORDERS ADD CONSTRAINT
> HEXORDERS_CONSTRAINT_FK1 FOREIGN KEY ( CUSTOMER_ID ) REFERENCES
> HEXCUSTOMERS ( CUSTOMER_ID ) MATCH FULL ;
>
>
> ALTER TABLE HEXORDERS ADD CONSTRAINT HEXORDERS_CONSTRAINT_FK2
> FOREIGN KEY ( DISTRIBUTOR_ID ) REFERENCES HEXDISTRIBUTORS
> ( DISTRIBUTOR_ID ) MATCH FULL ;
>
Within my postgres environmentPostgreSQL 7.2.3 on hppa-hp-hpux10.20, compiled by GCC 2.95.2
I've created table HEXORDERS and added HEXORDERS_CONSTRAINT_PK,
and did not add HEXORDERS_CONSTRAINT_FK1 nor HEXORDERS_CONSTRAINT_FK2,
because of no idea how HEXCUSTOMERS resp. HEXDISTRIBUTORS look like.
Then I did successfullyCREATE OR REPLACE FUNCTION orderinsert(INTEGER, VARCHAR) RETURNSINTEGER AS 'INSERT INTO
HEXORDERS( CUSTOMER_ID, ORDER_KEY, DISTRIBUTOR_ID,ORDER_AMOUNT, ORDER_GST ) VALUES ( $1, $2, 1, 0, 0 ) ;SELECT 1 ;'
LANGUAGESQL ;
Even a
SELECT orderinsert( 123,'abcdef' );
worked as intended (one row inserted).
Nothing about "parse error at or near ;"
So you find me pretty clueless about what's going wrong on your side.
Did you search the archives for hints on strange parser errors?
Regards, Christoph
PS Keep on posting to the list, maybe somebody else knows more.
I think the language needs to be in quotes ... ... ' language 'sql'; >>> "Jon Griffin" <jong@e88.org> 03/12/03 11:59AM >>> You need to put your aliases in: value1 alias for $1; etc. Hello, > > I am trying to create a database trigger which inserts into a second > table. I have created the following function in accordance with the > reference manual documentation (7.2). > > > CREATE OR REPLACE FUNCTION orderinsert(INTEGER, VARCHAR) RETURNS INTEGER > AS ' > > INSERT INTO HEXORDERS ( CUSTOMER_ID, ORDER_KEY, DISTRIBUTOR_ID, > ORDER_AMOUNT, ORDER_GST ) VALUES ( $1, $2, 1, 0, 0 ) ; > > SELECT 1 ; > > ' LANGUAGE SQL ; > > > > It fails with the cryptic error "parse error at or near ;". Can anyone > tell me what is wrong with this syntax? > > Regards ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
Unfortunately this did not solve the problem (I tried both upper and lower case). It is a strange one. I end up with the following 3 errors: ERROR: parser: parse error at or near "1" ERROR: parser: parse error at or near ";" ERROR: parser: parse error at or near "orderinsert" Regards ----- Original Message ----- From: "Ian Harding" <ianh@tpchd.org> To: <jong@e88.org>; <susan@hexworx.com> Cc: <pgsql-sql@postgresql.org> Sent: Thursday, March 13, 2003 10:45 PM Subject: Re: [SQL] Create function statement with insert statement > I think the language needs to be in quotes ... > > ... > ' language 'sql'; > > >>> "Jon Griffin" <jong@e88.org> 03/12/03 11:59AM >>> > You need to put your aliases in: > value1 alias for $1; > etc. > > Hello, > > > > I am trying to create a database trigger which inserts into a second > > table. I have created the following function in accordance with the > > reference manual documentation (7.2). > > > > > > CREATE OR REPLACE FUNCTION orderinsert(INTEGER, VARCHAR) RETURNS INTEGER > > AS ' > > > > INSERT INTO HEXORDERS ( CUSTOMER_ID, ORDER_KEY, DISTRIBUTOR_ID, > > ORDER_AMOUNT, ORDER_GST ) VALUES ( $1, $2, 1, 0, 0 ) ; > > > > SELECT 1 ; > > > > ' LANGUAGE SQL ; > > > > > > > > It fails with the cryptic error "parse error at or near ;". Can anyone > > tell me what is wrong with this syntax? > > > > Regards > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
Hi Chris,
Thanks for your efforts but seems like something very odd is going on
because even though I commented out the constraints I still received the
parse errors.
I searched the archives but looked for references to triggers and functions
rather than parse errors.
Regards
----- Original Message -----
From: "Christoph Haller" <ch@rodos.fzk.de>
To: <pgsql-sql@postgresql.org>
Cc: <susan@hexworx.com>
Sent: Thursday, March 13, 2003 9:15 PM
Subject: Re: [SQL] Create function statement with insert statement
> Hello Susan,
> >
> > The relevant code for creating the hexorders table (and associated
> > constraints) is:
> >
> >
> ************************************************************************
>
> > DROP TABLE HEXORDERS ;
> >
> > DROP SEQUENCE HEXORDERS_SEQ ;
> >
> > CREATE SEQUENCE HEXORDERS_SEQ START 1 ;
> >
> > CREATE TABLE HEXORDERS (
> > ORDER_ID INTEGER DEFAULT NEXTVAL('HEXORDERS_SEQ') NOT NULL,
> > CUSTOMER_ID INTEGER NOT NULL,
> > ORDER_AMOUNT NUMERIC(12,2),
> > ORDER_DISCOUNT_CODE CHARACTER(1),
> > ORDER_KEY VARCHAR(255),
> > DISTRIBUTOR_ID INTEGER,
> > ORDER_GST NUMERIC(12,2),
> > ORDER_SHIPPING_COST NUMERIC(12,2),
> > ORDER_DATE DATE DEFAULT CURRENT_DATE,
> > ORDER_VALID BOOLEAN DEFAULT 'FALSE',
> > ORDER_SHIPPING_DATE DATE,
> > ORDER_DELIVERY_DATETIME TIMESTAMP,
> > ORDER_FREIGHT_COMPANY VARCHAR(30),
> > ORDER_CLOSE_DATE DATE );
> >
> >
> > ALTER TABLE HEXORDERS ADD CONSTRAINT HEXORDERS_CONSTRAINT_PK
> > PRIMARY KEY ( ORDER_ID );
> >
> >
> > ALTER TABLE HEXORDERS ADD CONSTRAINT
> > HEXORDERS_CONSTRAINT_FK1 FOREIGN KEY ( CUSTOMER_ID ) REFERENCES
> > HEXCUSTOMERS ( CUSTOMER_ID ) MATCH FULL ;
> >
> >
> > ALTER TABLE HEXORDERS ADD CONSTRAINT HEXORDERS_CONSTRAINT_FK2
> > FOREIGN KEY ( DISTRIBUTOR_ID ) REFERENCES HEXDISTRIBUTORS
> > ( DISTRIBUTOR_ID ) MATCH FULL ;
> >
> Within my postgres environment
> PostgreSQL 7.2.3 on hppa-hp-hpux10.20, compiled by GCC 2.95.2
> I've created table HEXORDERS and added HEXORDERS_CONSTRAINT_PK,
> and did not add HEXORDERS_CONSTRAINT_FK1 nor HEXORDERS_CONSTRAINT_FK2,
> because of no idea how HEXCUSTOMERS resp. HEXDISTRIBUTORS look like.
> Then I did successfully
> CREATE OR REPLACE FUNCTION orderinsert(INTEGER, VARCHAR) RETURNS
> INTEGER AS '
> INSERT INTO HEXORDERS ( CUSTOMER_ID, ORDER_KEY, DISTRIBUTOR_ID,
> ORDER_AMOUNT, ORDER_GST ) VALUES ( $1, $2, 1, 0, 0 ) ;
> SELECT 1 ;
> ' LANGUAGE SQL ;
> Even a
> SELECT orderinsert( 123,'abcdef' );
> worked as intended (one row inserted).
>
> Nothing about "parse error at or near ;"
> So you find me pretty clueless about what's going wrong on your side.
> Did you search the archives for hints on strange parser errors?
>
> Regards, Christoph
>
> PS Keep on posting to the list, maybe somebody else knows more.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
> Hi Chris,
>
> Thanks for your efforts but seems like something very odd is going on
> because even though I commented out the constraints I still received
the
> parse errors.
>
> I searched the archives but looked for references to triggers and
functions
> rather than parse errors.
>
> Regards
>
Susan,
Could you check if the SQL procedural language is available at all by
select * from pg_language ;lanname | lanispl | lanpltrusted | lanplcallfoid | lanvalidator |
lanacl
----------+---------+--------------+---------------+--------------+--------
sql | f | t | 0 | 2248 | {=U}
internal | f | f | 0 | 2246 | {=}c | f | f | 0
| 2247 | {=}
(3 rows)
These three should show up by default; Maybe somebody dropped it
accidentally.
Regards, Christoph
Hi Susan, I had a similar problem with a function using pl/pgsql. I checked the docs (7.3) for it and discovered the following info: "A trigger procedure is created with the CREATE FUNCTION command as a function with no arguments and a return type of OPAQUE" http://www.postgresql.org/docs/view.php?version=7.2&idoc=1&file=plpgsql-trigger.html Regards, -Andreas > Hello, > > I am trying to create a database trigger which inserts into a second table. > I have created the following function in accordance with the reference > manual documentation (7.2). > > > CREATE OR REPLACE FUNCTION orderinsert(INTEGER, VARCHAR) RETURNS INTEGER AS > ' > > INSERT INTO HEXORDERS ( CUSTOMER_ID, ORDER_KEY, DISTRIBUTOR_ID, > ORDER_AMOUNT, ORDER_GST ) VALUES ( $1, $2, 1, 0, 0 ) ; > > SELECT 1 ; > > ' LANGUAGE SQL ; > > > > It fails with the cryptic error "parse error at or near ;". Can anyone > tell me what is wrong with this syntax? > > Regards -- Andreas Schmitz - Phone +49 201 8501 318 Cityweb-Technik-Service-Gesellschaft mbH Friedrichstr. 12 - Fax +49 201 8501 104 45128 Essen - email a.schmitz@cityweb.de
Hi Andrea, That was my first attempt (i.e. using opaque) but unfortunately when I use "opaque" it tells me that SQL functions cannot return opaque. The parse error is also occurring on the create function statement prior to any knowledge that the function is being used for a trigger. Regards ----- Original Message ----- From: "Andreas Schmitz" <a.schmitz@cityweb.de> To: "Susan Hoddinott" <susan@hexworx.com>; <pgsql-sql@postgresql.org> Sent: Friday, March 14, 2003 10:52 PM Subject: Re: [SQL] Create function statement with insert statement > > Hi Susan, > > I had a similar problem with a function using pl/pgsql. I checked the docs > (7.3) for it and discovered the following info: > > "A trigger procedure is created with the CREATE FUNCTION command as a function > with no arguments and a return type of OPAQUE" > > http://www.postgresql.org/docs/view.php?version=7.2&idoc=1&file=plpgsql-trig ger.html > > > Regards, > > -Andreas > > > > > > Hello, > > > > I am trying to create a database trigger which inserts into a second table. > > I have created the following function in accordance with the reference > > manual documentation (7.2). > > > > > > CREATE OR REPLACE FUNCTION orderinsert(INTEGER, VARCHAR) RETURNS INTEGER AS > > ' > > > > INSERT INTO HEXORDERS ( CUSTOMER_ID, ORDER_KEY, DISTRIBUTOR_ID, > > ORDER_AMOUNT, ORDER_GST ) VALUES ( $1, $2, 1, 0, 0 ) ; > > > > SELECT 1 ; > > > > ' LANGUAGE SQL ; > > > > > > > > It fails with the cryptic error "parse error at or near ;". Can anyone > > tell me what is wrong with this syntax? > > > > Regards > > -- > Andreas Schmitz - Phone +49 201 8501 318 > Cityweb-Technik-Service-Gesellschaft mbH > Friedrichstr. 12 - Fax +49 201 8501 104 > 45128 Essen - email a.schmitz@cityweb.de
Yes.
I can select from this table although the output is slightly different:
lanname | lanispl | lanpltrusted | lanplcallfoid | lancompiler
----------+---------+--------------+---------------+-------------internal | f | f | 0 |
n/aC | f | f | 0 | /bin/ccsql | f | f | 0 |
postgres
(3 rows)
Regards
----- Original Message -----
From: "Christoph Haller" <ch@rodos.fzk.de>
To: <pgsql-sql@postgresql.org>
Cc: <susan@hexworx.com>
Sent: Friday, March 14, 2003 11:05 PM
Subject: Re: [SQL] Create function statement with insert statement
> >
> > Hi Chris,
> >
> > Thanks for your efforts but seems like something very odd is going on
> > because even though I commented out the constraints I still received
> the
> > parse errors.
> >
> > I searched the archives but looked for references to triggers and
> functions
> > rather than parse errors.
> >
> > Regards
> >
> Susan,
>
> Could you check if the SQL procedural language is available at all by
>
> select * from pg_language ;
> lanname | lanispl | lanpltrusted | lanplcallfoid | lanvalidator |
> lanacl
> ----------+---------+--------------+---------------+--------------+-------
-
>
> sql | f | t | 0 | 2248 | {=U}
>
> internal | f | f | 0 | 2246 | {=}
> c | f | f | 0 | 2247 | {=}
> (3 rows)
>
> These three should show up by default; Maybe somebody dropped it
> accidentally.
>
> Regards, Christoph
>
Susan Hoddinott wrote: > Hi Andrea, > > That was my first attempt (i.e. using opaque) but unfortunately when I use > "opaque" it tells me that SQL functions cannot return opaque. The parse > error is also occurring on the create function statement prior to any > knowledge that the function is being used for a trigger. > I haven't followed this thread too closely, but if you are trying to use a SQL function for a trigger, it won't work. From the docs: (http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/triggers.html) "Trigger functions can be written in C and most procedural languages, but not in SQL" 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.html http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/sql-createlanguage.html HTH, Joe
Hi all, I want to make an aggregate function that concatenates strings from a certain column into one cell in a group by clause. For example I have the following table : Type Text ================= 1 text1 2 text2 1 text3 3 text4 2 text5 I need a query that group by type and concats the text columns to produce something like the following : 1 text1, text3 2 text2, text5 3 text4 I am aware that this can be done through recursive joins but this is too complicated and heavy. Any suggestions? Regards
Objectz wrote: > Hi all, > > I want to make an aggregate function that concatenates strings from a > certain column into one cell in a group by clause. > For example I have the following table : > > Type Text > ================= > 1 text1 > 2 text2 > 1 text3 > 3 text4 > 2 text5 > > I need a query that group by type and concats the text columns to > produce something like the following : > > 1 text1, text3 > 2 text2, text5 > 3 text4 > > I am aware that this can be done through recursive joins but this is too > complicated and heavy. > > Any suggestions? > > Regards Look at this site (Aggregate Functions): http://www.brasileiro.net:8080/postgres/cookbook/ Regards, Tomasz Myrta
Tomsaz,
Thnx a lot for the great reference It has what I need.
create function comma_aggregate(text,text) returns text as '
begin if (length($1) > 0 ) then return $1 || ', ' || $2; else return $2; end if;
end;
' language 'plpgsql';
-- create the aggregate function
create aggregate comma (basetype=text, sfunc=comma_aggregate,
stype=text, initcond='' );
I need some help to expand on its funcitonality instead of calling
comma(col_name) to produce comma-seperated values I need to call
something like concattext(prefix, col_name, suffix) wich returns the
list in one column with the prefix and suffix aroung each entry as
specified.
This will enable me to produce something like this result
> 1 (text1) (text3)
> 2 (text2) (text5)
> 3 (text4)
With this query
Select type, concattext('(', text, ') ') from table group by type
Any help on how to convert the above comma function to receive the
prefix and suffix parameters
Regards
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Tomasz Myrta
Sent: Monday, March 17, 2003 11:16 AM
To: Objectz
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] String aggregate function
Objectz wrote:
> Hi all,
>
> I want to make an aggregate function that concatenates strings from a
> certain column into one cell in a group by clause. For example I have
> the following table :
>
> Type Text
> =================
> 1 text1
> 2 text2
> 1 text3
> 3 text4
> 2 text5
>
> I need a query that group by type and concats the text columns to
> produce something like the following :
>
> 1 text1, text3
> 2 text2, text5
> 3 text4
>
> I am aware that this can be done through recursive joins but this is
> too complicated and heavy.
>
> Any suggestions?
>
> Regards
Look at this site (Aggregate Functions):
http://www.brasileiro.net:8080/postgres/cookbook/
Regards,
Tomasz Myrta
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Uz.ytkownik Objectz napisa?:
> Tomsaz,
>
> Thnx a lot for the great reference It has what I need.
>
> create function comma_aggregate(text,text) returns text as '
> begin
> if (length($1) > 0 ) then
> return $1 || ', ' || $2;
> else
> return $2;
> end if;
> end;
> ' language 'plpgsql';
>
> -- create the aggregate function
>
> create aggregate comma (basetype=text, sfunc=comma_aggregate,
> stype=text, initcond='' );
>
>
> I need some help to expand on its funcitonality instead of calling
> comma(col_name) to produce comma-seperated values I need to call
> something like concattext(prefix, col_name, suffix) wich returns the
> list in one column with the prefix and suffix aroung each entry as
> specified.
>
> This will enable me to produce something like this result
>
>
>>1 (text1) (text3)
>>2 (text2) (text5)
>>3 (text4)
>
>
> With this query
> Select type, concattext('(', text, ') ') from table group by type
>
> Any help on how to convert the above comma function to receive the
> prefix and suffix parameters
>
> Regards
Change this function to separate fields only with space character
(instead of comma) and use as below:
select comma('(' || text ')') ...
Regards,
Tomasz Myrta
Uz.ytkownik Tomasz Myrta napisa?:
> Change this function to separate fields only with space character
> (instead of comma) and use as below:
>
> select comma('(' || text ')') ...
Sorry:
select comma('(' || text || ')') ...
Tomasz
> > 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.html > http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/sql-createlanguage.html > I think Joe is right. Try PL/pgSQL, it's much more flexible anyway. Regards, Christoph
Didn't know this was possible.
Thnx a lot for ur support
-----Original Message-----
From: Tomasz Myrta [mailto:jasiek@klaster.net]
Sent: Monday, March 17, 2003 12:59 PM
To: Objectz
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] String aggregate function
Uz.ytkownik Tomasz Myrta napisa?:
> Change this function to separate fields only with space character
> (instead of comma) and use as below:
>
> select comma('(' || text ')') ...
Sorry:
select comma('(' || text || ')') ...
Tomasz
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