Re: inconsistent automatic casting between psql and function

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