pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCreate function statement with insert statement
14+ messages / 6 participants
[nested] [flat]
* Create function statement with insert statement
@ 2003-03-12 09:03 Susan Hoddinott <susan@hexworx.com>
0 siblings, 2 replies; 14+ messages in thread
From: Susan Hoddinott @ 2003-03-12 09:03 UTC (permalink / raw)
To: pgsql-sql
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
Attachments:
Susan Hoddinott.vcf (application/octet-stream, 170B) download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Create function statement with insert statement
@ 2003-03-12 19:59 Jon Griffin <jong@e88.org>
parent: Susan Hoddinott <susan@hexworx.com>
1 sibling, 0 replies; 14+ messages in thread
From: Jon Griffin @ 2003-03-12 19:59 UTC (permalink / raw)
To: susan@hexworx.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Create function statement with insert statement
@ 2003-03-13 10:07 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 14+ messages in thread
From: Christoph Haller @ 2003-03-13 10:07 UTC (permalink / raw)
To: pgsql-sql; +Cc: susan@hexworx.com
>
> 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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Create function statement with insert statement
@ 2003-03-13 13:15 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 14+ messages in thread
From: Christoph Haller @ 2003-03-13 13:15 UTC (permalink / raw)
To: pgsql-sql; +Cc: susan@hexworx.com
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.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Create function statement with insert statement
@ 2003-03-13 14:45 Ian Harding <ianh@tpchd.org>
0 siblings, 1 reply; 14+ messages in thread
From: Ian Harding @ 2003-03-13 14:45 UTC (permalink / raw)
To: jong@e88.org; susan@hexworx.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Create function statement with insert statement
@ 2003-03-14 14:28 Susan Hoddinott <susan@hexworx.com>
parent: Ian Harding <ianh@tpchd.org>
0 siblings, 0 replies; 14+ messages in thread
From: Susan Hoddinott @ 2003-03-14 14:28 UTC (permalink / raw)
To: Ian Harding <ianh@tpchd.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Create function statement with insert statement
@ 2003-03-14 14:52 Susan Hoddinott <susan@hexworx.com>
parent: Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 14+ messages in thread
From: Susan Hoddinott @ 2003-03-14 14:52 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Create function statement with insert statement
@ 2003-03-14 14:52 Andreas Schmitz <a.schmitz@cityweb.de>
parent: Susan Hoddinott <susan@hexworx.com>
1 sibling, 1 reply; 14+ messages in thread
From: Andreas Schmitz @ 2003-03-14 14:52 UTC (permalink / raw)
To: Susan Hoddinott <susan@hexworx.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Create function statement with insert statement
@ 2003-03-14 15:05 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 14+ messages in thread
From: Christoph Haller @ 2003-03-14 15:05 UTC (permalink / raw)
To: pgsql-sql; +Cc: susan@hexworx.com
>
> 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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Create function statement with insert statement
@ 2003-03-14 18:54 Susan Hoddinott <susan@hexworx.com>
parent: Andreas Schmitz <a.schmitz@cityweb.de>
0 siblings, 1 reply; 14+ messages in thread
From: Susan Hoddinott @ 2003-03-14 18:54 UTC (permalink / raw)
To: a.schmitz@cityweb.de; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Create function statement with insert statement
@ 2003-03-14 18:58 Susan Hoddinott <susan@hexworx.com>
parent: Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 14+ messages in thread
From: Susan Hoddinott @ 2003-03-14 18:58 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql
Yes.
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)
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
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Create function statement with insert statement
@ 2003-03-14 19:37 Joe Conway <mail@joeconway.com>
parent: Susan Hoddinott <susan@hexworx.com>
0 siblings, 0 replies; 14+ messages in thread
From: Joe Conway @ 2003-03-14 19:37 UTC (permalink / raw)
To: Susan Hoddinott <susan@hexworx.com>; +Cc: a.schmitz@cityweb.de; pgsql-sql
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Create function statement with insert statement
@ 2003-03-17 11:25 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 14+ messages in thread
From: Christoph Haller @ 2003-03-17 11:25 UTC (permalink / raw)
To: pgsql-sql; +Cc: susan@hexworx.com
>
> 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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Create function statement with insert statement
@ 2003-03-18 02:01 Susan Hoddinott <susan@hexworx.com>
parent: Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 14+ messages in thread
From: Susan Hoddinott @ 2003-03-18 02:01 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: 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_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
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2003-03-18 02:01 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-12 09:03 Create function statement with insert statement Susan Hoddinott <susan@hexworx.com>
2003-03-12 19:59 ` Jon Griffin <jong@e88.org>
2003-03-14 14:52 ` Andreas Schmitz <a.schmitz@cityweb.de>
2003-03-14 18:54 ` Susan Hoddinott <susan@hexworx.com>
2003-03-14 19:37 ` Joe Conway <mail@joeconway.com>
2003-03-13 10:07 Re: Create function statement with insert statement Christoph Haller <ch@rodos.fzk.de>
2003-03-13 13:15 Re: Create function statement with insert statement Christoph Haller <ch@rodos.fzk.de>
2003-03-14 14:52 ` Susan Hoddinott <susan@hexworx.com>
2003-03-13 14:45 Re: Create function statement with insert statement Ian Harding <ianh@tpchd.org>
2003-03-14 14:28 ` Susan Hoddinott <susan@hexworx.com>
2003-03-14 15:05 Re: Create function statement with insert statement Christoph Haller <ch@rodos.fzk.de>
2003-03-14 18:58 ` Susan Hoddinott <susan@hexworx.com>
2003-03-17 11:25 Re: Create function statement with insert statement Christoph Haller <ch@rodos.fzk.de>
2003-03-18 02:01 ` Susan Hoddinott <susan@hexworx.com>
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