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