I am attempting to get a plpgsql function to work (the function is listed below and the server is psql 7.2.3) and I keep getting alternating errors. When called once the error seen is:
NOTICE: Error occurred while executing PL/pgSQL function ordcred_internal
NOTICE: Parser: parse error at or near "$1"
I have been unable to find where such an error could be. When I call the fucntion a second time a different error appears:
NOTICE: LockRelease: holder table corrupted
NOTICE: Buffer Leak: [1068] (freeNext=-3, freePrev=-3, rel=16556/16702, blockNum=1, flags=0x185, refcount=-23 -1)
NOTICE: Buffer Leak: [10009] (freeNext=18899, freePrev=24441, rel=16556/16727, blockNum=0, flags=0x14, refcount=-1 -2)
NOTICE: Buffer Leak: [24504] (freeNext=-3, freePrev=-3, rel=16556/16697, blockNum=72, flags=0x4, refcount=-24 -1)
Each time this function is called these errors alternate. Any idea what is going on??? Thanks for your help.
JR
CREATE OR REPLACE FUNCTION ordcred_INTERNAL ( integer, double precision ) RETURNS double precision AS '
DECLARE
difference double precision;
credit_value double precision;
customer_id integer;
new_coupon_id integer;
coupon_title text;
BEGIN
SELECT INTO credit_value value FROM coupon WHERE id = $1;
IF credit_value > $2 THEN
difference := credit_value - $2;
ELSE
difference := 0;
END IF;
IF difference > 0 THEN
INSERT INTO coupon (date_start, date_end, type, static, value, issued, enabled, customer_credit, title, body ) VALUES ( (SELECT date_start FROM coupon WHERE id = $1), (SELECT date_end FROM coupon WHERE id = $1), (SELECT type FROM coupon WHERE id = $1), (SELECT static FROM coupon WHERE id = $1), difference, (SELECT issued FROM coupon WHERE id = $1), (SELECT enabled FROM coupon WHERE id = $1), 1, (SELECT title FROM coupon WHERE id = $1), (SELECT body FROM coupon WHERE id = $1) );
SELECT INTO customer_id customer_id FROM coupon_customer WHERE coupon_id = $1;
SELECT INTO coupon_title title FROM coupon WHERE id = $1 AND value = difference;
SELECT INTO new_coupon_id max( id ) FROM coupon WHERE title = coupon_title;
INSERT INTO coupon_customer (customer_id, coupon_id) VALUES ( customer_id, new_coupon_id );
UPDATE coupon SET value = $2 WHERE id = $1;
END IF; RETURN float8smaller( credit_value, $2 );
END; ' LANGUAGE 'plpgsql';
-- Best Regards,
Jeff Read Chief Technical Officer Pic'n'Del Systems Inc.
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
