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.



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Jeffery Read <jread (AT) picndel (DOT) com> writes:
I think you're hitting 7.2 bugs. If you can reproduce the misbehavior
in 7.3.4 then I'd be interested to see all the details ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)