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