Re: BUG #5043: Stored procedure returning different results for same arguments

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 <=3D $2  AND contractid =3D $1;
$$ LANGUAGE sql;

has it same behave like plpgsql function?

regards
Pavel Stehule

2009/9/8 Martin Edlman <edlman@fortech.cz>:
>
> The following bug has been logged online:
>
> Bug reference: =C2=A0 =C2=A0 =C2=A05043
> Logged by: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Martin Edlman
> Email address: =C2=A0 =C2=A0 =C2=A0edlman@fortech.cz
> PostgreSQL version: 8.2.0
> Operating system: =C2=A0 Linux (RHEL 4.4)
> Description: =C2=A0 =C2=A0 =C2=A0 =C2=A0Stored procedure returning differ=
ent 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_f=
rom
> (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 g=
et
> 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 th=
en
> passed to get_schemebind_date().
>
> I enabled RAISE NOTICE to see the parameters and the result, in case I ca=
ll
> get_schemebind_date() from console I see
> NOTICE: get_schemebind_date(1004, 2009-09-01) =3D 2009-09-01
> In case get_schemebind_date() is called from xfunc() I see
> NOTICE: get_schemebind_date(1004, 2009-09-01) =3D 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)
> =C2=A0RETURNS date AS
> $BODY$
> DECLARE
> =C2=A0 =C2=A0 =C2=A0 =C2=A0rec RECORD;
> =C2=A0 =C2=A0 =C2=A0 =C2=A0con ALIAS FOR $1;
> =C2=A0 =C2=A0 =C2=A0 =C2=A0dat ALIAS FOR $2;
> BEGIN
> =C2=A0 =C2=A0 =C2=A0 =C2=A0SELECT max(valid_from) AS vf
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0INTO rec
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0FROM schemebind
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0WHERE valid_from <=
=3D dat AND contractid =3D con;
> =C2=A0 =C2=A0 =C2=A0 =C2=A0RAISE NOTICE 'get_schemebind_date(%, %) =3D %'=
, con, dat, rec.vf;
> =C2=A0 =C2=A0 =C2=A0 =C2=A0IF FOUND THEN
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0RETURN rec.vf;
> =C2=A0 =C2=A0 =C2=A0 =C2=A0END IF;
> =C2=A0 =C2=A0 =C2=A0 =C2=A0RETURN 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
>