Hi,
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.