Hello please send function xfunc. Your code looks well.
please try sql function CREATE OR REPLACE FUNCTION get_schemebind_date(integer, date) RETURNS date AS $$ SELECT max(valid_from) FROM schemebind WHERE valid_from <= $2 AND contractid = $1; $$ LANGUAGE sql; has it same behave like plpgsql function? regards Pavel Stehule 2009/9/8 Martin Edlman <edl...@fortech.cz>: > > 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 > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
