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@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