The following bug has been logged online: Bug reference: 5043 Logged by: Martin Edlman Email address: edl...@fortech.cz PostgreSQL version: 8.2.0 Operating system: Linux (RHEL 4.4) Description: Stored procedure returning different results for same arguments Details:
I have a stored procedure (SP) get_schemebind_date(int, date) which looks up a date of validity of a payment scheme. Table schemebind contains valid_from (date), schemeid (int), contractid (int). There are these records in schemebind '2008-11-01', 123, 1004 '2009-09-01', 456, 1004 When I call "select get_schemebind_date(1004,'2009-09-01')" from psql I get correct result, which is 2009-09-01. But when I call it from within another sp (let's call it xfunc(int, date, varchar), I get wrong result 2008-11-01. The date passed to xfunc() is then passed to get_schemebind_date(). I enabled RAISE NOTICE to see the parameters and the result, in case I call get_schemebind_date() from console I see NOTICE: get_schemebind_date(1004, 2009-09-01) = 2009-09-01 In case get_schemebind_date() is called from xfunc() I see NOTICE: get_schemebind_date(1004, 2009-09-01) = 2008-11-01 I really don't understand it. All parameters are of type date, column valid_from is of type date as well. If you need full code of xfunc, I can send it to you. CREATE OR REPLACE FUNCTION get_schemebind_date(integer, date) RETURNS date AS $BODY$ DECLARE rec RECORD; con ALIAS FOR $1; dat ALIAS FOR $2; BEGIN SELECT max(valid_from) AS vf INTO rec FROM schemebind WHERE valid_from <= dat AND contractid = con; RAISE NOTICE 'get_schemebind_date(%, %) = %', con, dat, rec.vf; IF FOUND THEN RETURN rec.vf; END IF; RETURN NULL; END $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION get_schemebind_date(integer, date) OWNER TO postgres; -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs