From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Stefano Buliani <stefano(at)covestor(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: inconsistent automatic casting between psql and function |
Date: | 2008-12-09 18:02:10 |
Message-ID: | 493EB2A2.108@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Kellerer | 2008-12-09 18:05:10 | Re: store pdf files |
Previous Message | Frank Bax | 2008-12-09 17:57:15 | Re: store pdf files |