Re: 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

----- Original Message -----
From: "Christoph Haller" <ch(at)rodos(dot)fzk(dot)de>
To: <pgsql-sql(at)postgresql(dot)org>
Cc: <susan(at)hexworx(dot)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly