I am trying to create a database trigger w= hich=20 inserts into a second table. I have created the following function in= =20 accordance with the reference manual documentation (7.2).

CREATE OR REPLACE FUNCTION orderinsert(INTEGER, VARCHAR) RETURNS INTEGER= AS=20 '

INSERT INTO HEXORDERS ( CUSTOMER_ID, ORDER_KEY, DISTRIBUTOR_ID, ORDER_AM= OUNT,=20 ORDER_GST ) VALUES ( $1, $2, 1, 0, 0 ) ;

SELECT 1 ;

' LANGUAGE SQL ;

It fails with the cryptic error "parse error at or near ;". = Can=20 anyone tell me what is wrong with this syntax?

Regards

------=_NextPart_001_002F_01C2E8B9.40A45320-- ------=_NextPart_000_002E_01C2E8B9.40A45320 Content-Type: application/octet-stream; name="Susan Hoddinott.vcf" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="Susan Hoddinott.vcf" BEGIN:VCARD VERSION:2.1 N:Hoddinott;Susan FN:Susan Hoddinott EMAIL;INTERNET:susan@perth.dialix.com.au EMAIL;PREF;INTERNET:susan@hexworx.com REV:20030312T090307Z END:VCARD ------=_NextPart_000_002E_01C2E8B9.40A45320-- From jong@e88.org Wed Mar 12 19:59:08 2003 X-Original-To: pgsql-sql@postgresql.org Received: from zuma.wtgcom.com (unknown [208.48.171.162]) by postgresql.org (Postfix) with ESMTP id A382F476364 for ; Wed, 12 Mar 2003 14:59:08 -0500 (EST) Received: (qmail 31218 invoked by uid 204); 12 Mar 2003 20:21:02 -0000 Received: from jong@e88.org by zuma.wtgcom.com by uid 1000 with qmail-scanner-1.15 (sweep: 2.10/3.65. Clear:. Processed in 0.851738 secs); 12 Mar 2003 20:21:02 -0000 X-Qmail-Scanner-Mail-From: jong@e88.org via zuma.wtgcom.com X-Qmail-Scanner: 1.15 (Clear:. Processed in 0.851738 secs) Received: from unknown (HELO mayuli.com) (192.168.1.202) by 0 with SMTP; 12 Mar 2003 20:21:01 -0000 Received: from 192.168.1.12 (proxying for 129.219.231.66) (SquirrelMail authenticated user jon_clean@mayuli.com) by mail.mayuli.com with HTTP; Wed, 12 Mar 2003 11:59:12 -0800 (PST) Message-ID: <38008.192.168.1.12.1047499152.squirrel@mail.mayuli.com> Date: Wed, 12 Mar 2003 11:59:12 -0800 (PST) Subject: Re: Create function statement with insert statement From: "Jon Griffin" To: In-Reply-To: <003201c2e876$3498a880$1f84fea9@oemcomputer> References: <003201c2e876$3498a880$1f84fea9@oemcomputer> X-Priority: 3 Importance: Normal Cc: X-Mailer: SquirrelMail (version 1.2.11) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Archive-Number: 200303/306 X-Sequence-Number: 12345 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 From ch@rodos.fzk.de Thu Mar 13 10:07:45 2003 X-Original-To: pgsql-sql@postgresql.org Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52]) by postgresql.org (Postfix) with SMTP id E710B474E44 for ; Thu, 13 Mar 2003 05:07:45 -0500 (EST) Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ; Thu Mar 13 11:07:46 2003 +0100 Received: śby rodos.fzk.de with ESMTP (8.8.6 (PHNE_17135)/8.7.1) id LAA01120; Thu, 13 Mar 2003 11:07:45 +0100 (MET) Message-ID: <3E705871.E0CBB9E@rodos.fzk.de> Date: Thu, 13 Mar 2003 11:07:45 +0100 From: Christoph Haller X-Mailer: Mozilla 4.77 [en] (X11; U; HP-UX B.10.20 9000/780) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Cc: susan@hexworx.com Subject: Re: Create function statement with insert statement Content-Type: text/plain; charset=iso-8859-2 Content-Transfer-Encoding: 7bit X-Archive-Number: 200303/330 X-Sequence-Number: 12369 > > 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 From ch@rodos.fzk.de Thu Mar 13 13:15:12 2003 X-Original-To: pgsql-sql@postgresql.org Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52]) by postgresql.org (Postfix) with SMTP id 65091476348 for ; Thu, 13 Mar 2003 08:15:12 -0500 (EST) Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ; Thu Mar 13 14:15:14 2003 +0100 Received: śby rodos.fzk.de with ESMTP (8.8.6 (PHNE_17135)/8.7.1) id OAA01321; Thu, 13 Mar 2003 14:15:14 +0100 (MET) Message-ID: <3E708461.1C7A2AD3@rodos.fzk.de> Date: Thu, 13 Mar 2003 14:15:13 +0100 From: Christoph Haller X-Mailer: Mozilla 4.77 [en] (X11; U; HP-UX B.10.20 9000/780) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Cc: susan@hexworx.com Subject: Re: Create function statement with insert statement Content-Type: text/plain; charset=iso-8859-2 Content-Transfer-Encoding: 7bit X-Archive-Number: 200303/331 X-Sequence-Number: 12370 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. From ianh@tpchd.org Thu Mar 13 14:47:11 2003 X-Original-To: pgsql-sql@postgresql.org Received: from mail.tpchd.org (unknown [192.103.155.40]) by postgresql.org (Postfix) with SMTP id 2FB52476CD3 for ; Thu, 13 Mar 2003 09:47:11 -0500 (EST) Received: from TPC-Message_Server by mail.tpchd.org with Novell_GroupWise; Thu, 13 Mar 2003 06:46:52 -0800 Message-Id: X-Mailer: Novell GroupWise Internet Agent 5.5.2.1 Date: Thu, 13 Mar 2003 06:45:19 -0800 From: "Ian Harding" To: , Cc: Subject: Re: Create function statement with insert statement Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Archive-Number: 200303/353 X-Sequence-Number: 12392 I think the language needs to be in quotes ... ... ' language 'sql'; >>> "Jon Griffin" 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 From susan@hexworx.com Fri Mar 14 14:30:04 2003 X-Original-To: pgsql-sql@postgresql.org Received: from sydney1.qnix.com.au (unknown [203.98.67.20]) by postgresql.org (Postfix) with ESMTP id 8CE92476371 for ; Fri, 14 Mar 2003 09:30:04 -0500 (EST) Received: from oemcomputer (dialup-16.37.194.203.acc01-stge-pth.comindico.com.au [203.194.37.16]) (authenticated) by sydney1.qnix.com.au (8.11.6/8.11.6) with ESMTP id h2EEZlQ26938; Sat, 15 Mar 2003 01:35:49 +1100 Message-ID: <022001c2ea36$072b3180$1f84fea9@oemcomputer> Reply-To: "Susan Hoddinott" From: "Susan Hoddinott" To: "Ian Harding" Cc: References: Subject: Re: Create function statement with insert statement Date: Fri, 14 Mar 2003 22:28:46 +0800 Organization: Hexworx MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2919.6600 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600 X-Archive-Number: 200303/361 X-Sequence-Number: 12400 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" To: ; Cc: 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" 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 From susan@hexworx.com Fri Mar 14 14:52:28 2003 X-Original-To: pgsql-sql@postgresql.org Received: from sydney1.qnix.com.au (unknown [203.98.67.20]) by postgresql.org (Postfix) with ESMTP id 4B54747634A for ; Fri, 14 Mar 2003 09:52:28 -0500 (EST) Received: from oemcomputer (dialup-16.37.194.203.acc01-stge-pth.comindico.com.au [203.194.37.16]) (authenticated) by sydney1.qnix.com.au (8.11.6/8.11.6) with ESMTP id h2EExEQ28219; Sat, 15 Mar 2003 01:59:15 +1100 Message-ID: <000b01c2ea39$4d438020$1f84fea9@oemcomputer> Reply-To: "Susan Hoddinott" From: "Susan Hoddinott" To: "Christoph Haller" Cc: References: <3E708461.1C7A2AD3@rodos.fzk.de> Subject: Re: Create function statement with insert statement Date: Fri, 14 Mar 2003 22:52:11 +0800 Organization: Hexworx MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2919.6600 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600 X-Archive-Number: 200303/362 X-Sequence-Number: 12401 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" To: Cc: 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 From a.schmitz@cityweb.de Fri Mar 14 15:08:37 2003 X-Original-To: pgsql-sql@postgresql.org Received: from linux012.intern.cityweb.de (linux012.intern.cityweb.de [195.71.215.252]) by postgresql.org (Postfix) with ESMTP id BABED476392 for ; Fri, 14 Mar 2003 10:08:37 -0500 (EST) Received: from zahedi.intern.cityweb.de (zahedi.intern.cityweb.de [193.189.226.198]) by linux012.intern.cityweb.de (8.11.0/8.11.0) with ESMTP id h2EF8YJ24434; Fri, 14 Mar 2003 16:08:35 +0100 Content-Type: text/plain; charset="iso-8859-1" From: Andreas Schmitz Reply-To: a.schmitz@cityweb.de Organization: Cityweb Network To: "Susan Hoddinott" , Subject: Re: Create function statement with insert statement Date: Fri, 14 Mar 2003 15:52:38 +0100 User-Agent: KMail/1.4.1 References: <003201c2e876$3498a880$1f84fea9@oemcomputer> In-Reply-To: <003201c2e876$3498a880$1f84fea9@oemcomputer> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: <200303141552.38325.a.schmitz@cityweb.de> X-Archive-Number: 200303/364 X-Sequence-Number: 12403 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 From ch@rodos.fzk.de Fri Mar 14 15:05:26 2003 X-Original-To: pgsql-sql@postgresql.org Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52]) by postgresql.org (Postfix) with SMTP id E70974763A2 for ; Fri, 14 Mar 2003 10:05:26 -0500 (EST) Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ; Fri Mar 14 16:05:30 2003 +0100 Received: śby rodos.fzk.de with ESMTP (8.8.6 (PHNE_17135)/8.7.1) id QAA03064; Fri, 14 Mar 2003 16:05:30 +0100 (MET) Message-ID: <3E71EFB9.CD27D06B@rodos.fzk.de> Date: Fri, 14 Mar 2003 16:05:29 +0100 From: Christoph Haller X-Mailer: Mozilla 4.77 [en] (X11; U; HP-UX B.10.20 9000/780) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Cc: susan@hexworx.com Subject: Re: Create function statement with insert statement Content-Type: text/plain; charset=iso-8859-2 Content-Transfer-Encoding: 7bit X-Archive-Number: 200303/363 X-Sequence-Number: 12402 > > 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 From susan@hexworx.com Fri Mar 14 18:54:47 2003 X-Original-To: pgsql-sql@postgresql.org Received: from sydney1.qnix.com.au (unknown [203.98.67.20]) by postgresql.org (Postfix) with ESMTP id EF82D474E44 for ; Fri, 14 Mar 2003 13:54:47 -0500 (EST) Received: from oemcomputer (dialup-132.39.194.203.acc02-stge-pth.comindico.com.au [203.194.39.132]) (authenticated) by sydney1.qnix.com.au (8.11.6/8.11.6) with ESMTP id h2EJ1HQ15082; Sat, 15 Mar 2003 06:01:17 +1100 Message-ID: <002a01c2ea5b$1c0e06c0$1f84fea9@oemcomputer> Reply-To: "Susan Hoddinott" From: "Susan Hoddinott" To: Cc: References: <003201c2e876$3498a880$1f84fea9@oemcomputer> <200303141552.38325.a.schmitz@cityweb.de> Subject: Re: Create function statement with insert statement Date: Sat, 15 Mar 2003 02:54:13 +0800 Organization: Hexworx MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2919.6600 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600 X-Archive-Number: 200303/370 X-Sequence-Number: 12409 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" To: "Susan Hoddinott" ; 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 From susan@hexworx.com Fri Mar 14 18:58:42 2003 X-Original-To: pgsql-sql@postgresql.org Received: from sydney1.qnix.com.au (unknown [203.98.67.20]) by postgresql.org (Postfix) with ESMTP id AB94147590C for ; Fri, 14 Mar 2003 13:58:42 -0500 (EST) Received: from oemcomputer (dialup-132.39.194.203.acc02-stge-pth.comindico.com.au [203.194.39.132]) (authenticated) by sydney1.qnix.com.au (8.11.6/8.11.6) with ESMTP id h2EJ5QQ15412; Sat, 15 Mar 2003 06:05:27 +1100 Message-ID: <002b01c2ea5b$b0e95380$1f84fea9@oemcomputer> Reply-To: "Susan Hoddinott" From: "Susan Hoddinott" To: "Christoph Haller" Cc: References: <3E71EFB9.CD27D06B@rodos.fzk.de> Subject: Re: Create function statement with insert statement Date: Sat, 15 Mar 2003 02:58:22 +0800 Organization: Hexworx MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2919.6600 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600 X-Archive-Number: 200303/371 X-Sequence-Number: 12410 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" To: Cc: 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 > From mail@joeconway.com Fri Mar 14 19:40:17 2003 X-Original-To: pgsql-sql@postgresql.org Received: from joeconway.com (66-146-172-86.skyriver.net [66.146.172.86]) by postgresql.org (Postfix) with ESMTP id B646D47590C for ; Fri, 14 Mar 2003 14:40:17 -0500 (EST) Received: from [206.19.64.3] (account jconway HELO joeconway.com) by joeconway.com (CommuniGate Pro SMTP 4.0.4) with ESMTP-TLS id 1664422; Fri, 14 Mar 2003 12:16:13 -0800 Message-ID: <3E722F91.6070506@joeconway.com> Date: Fri, 14 Mar 2003 11:37:53 -0800 From: Joe Conway User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.2.1) Gecko/20021130 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Susan Hoddinott Cc: a.schmitz@cityweb.de, pgsql-sql@postgresql.org Subject: Re: Create function statement with insert statement References: <003201c2e876$3498a880$1f84fea9@oemcomputer> <200303141552.38325.a.schmitz@cityweb.de> <002a01c2ea5b$1c0e06c0$1f84fea9@oemcomputer> In-Reply-To: <002a01c2ea5b$1c0e06c0$1f84fea9@oemcomputer> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Archive-Number: 200303/372 X-Sequence-Number: 12411 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 From ch@rodos.fzk.de Mon Mar 17 11:25:48 2003 X-Original-To: pgsql-sql@postgresql.org Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52]) by postgresql.org (Postfix) with SMTP id 350F2474E53 for ; Mon, 17 Mar 2003 06:25:48 -0500 (EST) Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ; Mon Mar 17 12:25:49 2003 +0100 Received: śby rodos.fzk.de with ESMTP (8.8.6 (PHNE_17135)/8.7.1) id MAA06463; Mon, 17 Mar 2003 12:25:48 +0100 (MET) Message-ID: <3E75B0BB.1F631FDC@rodos.fzk.de> Date: Mon, 17 Mar 2003 12:25:47 +0100 From: Christoph Haller X-Mailer: Mozilla 4.77 [en] (X11; U; HP-UX B.10.20 9000/780) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Cc: susan@hexworx.com Subject: Re: Create function statement with insert statement Content-Type: text/plain; charset=iso-8859-2 Content-Transfer-Encoding: 7bit X-Archive-Number: 200303/388 X-Sequence-Number: 12427 > > 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 From susan@hexworx.com Tue Mar 18 02:01:50 2003 X-Original-To: pgsql-sql@postgresql.org Received: from sydney1.qnix.com.au (unknown [203.98.67.20]) by postgresql.org (Postfix) with ESMTP id 36D41476178 for ; Mon, 17 Mar 2003 21:01:50 -0500 (EST) Received: from oemcomputer (dialup-149.38.194.203.acc02-stge-pth.comindico.com.au [203.194.38.149]) (authenticated) by sydney1.qnix.com.au (8.11.6/8.11.6) with ESMTP id h2I28kQ18972; Tue, 18 Mar 2003 13:08:47 +1100 Message-ID: <000801c2ecf2$4484f480$1f84fea9@oemcomputer> Reply-To: "Susan Hoddinott" From: "Susan Hoddinott" To: "Christoph Haller" Cc: References: <3E75B0BB.1F631FDC@rodos.fzk.de> Subject: Re: Create function statement with insert statement Date: Tue, 18 Mar 2003 10:01:16 +0800 Organization: Hexworx MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2919.6600 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600 X-Archive-Number: 200303/422 X-Sequence-Number: 12461 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" To: Cc: 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