Stefano Buliani wrote: > If I run this query from the psql client it works just fine. From the > function it doesn't return anything. > What I discovered is that for it to work from the function I need to > explicitly cast the tradedate variable to DATE (ie '2008-12-08 > 02:00:00'::DATE - Note the field is of type date). > > It would seem that the psql client casts the value automatically. > Any reason why this should be? > This inconsistent behaviour makes code much harder to debug.
Seems unlikely. Can't reproduce the problem assuming you're using a quoted literal as your query shows. => CREATE FUNCTION date_test() RETURNS boolean AS $$BEGIN RETURN current_date = '2008-12-09 02:00:00'; END;$$ LANGUAGE plpgsql; CREATE FUNCTION => SELECT date_test(); date_test ----------- t => SELECT current_date = '2008-12-09 02:00:00'; ?column? ---------- t On the other hand, if you are using variable interpolation: CREATE OR REPLACE FUNCTION date_test2(timestamp) RETURNS boolean AS $$BEGIN RETURN current_date = $1; END;$$ LANGUAGE plpgsql; CREATE FUNCTION => SELECT date_test2('2008-12-09 02:00:00'); date_test2 ------------ f => SELECT current_date = '2008-12-09 02:00:00'::timestamp; ?column? ---------- f That's because a quoted literal isn't necessarily a timestamp. Without context it could be anything, and in the context of comparing to a date the planner probably tries to make it a date. Your variable is definitely a timestamp though (you've said so explicitly) so PG has to decide what it means to compare a date to a timestamp. It decides the reasonable approach is to turn the date into a timestamp (by adding '00:00:00' to it) and then the comparison fails. That seems reasonable to me - you're unlikely to want to discard information from an equality test. The obvious question is - why are you comparing a date to a timestamp in the first place? -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql