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

Reply via email to