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