From: Christoph Haller <ch@rodos.fzk.de>
To: pgsql-sql@postgresql.org
Cc: susan@hexworx.com
Subject: Re: Create function statement with insert statement
Date: Thu, 13 Mar 2003 14:15:13 +0100
Message-ID: <3E708461.1C7A2AD3@rodos.fzk.de> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de, susan@hexworx.com
Subject: Re: Create function statement with insert statement
In-Reply-To: <3E708461.1C7A2AD3@rodos.fzk.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox