Thread: BUG #1022: date calculation forces wrong type in function parameter and causes error

The following bug has been logged online:

Bug reference:      1022
Logged by:          Bruce Patin
Email address:      bpatin@padecs.riss.net
PostgreSQL version: 7.4
Operating system:   FreeBSD 4.8-RELEASE
Description:        date calculation forces wrong type in function parameter and causes error
Details:

In PostgreSQL 7.4 only, a date type provided as a function parameter gets automatically typecast to 'timestamp without
timezone' when calculations are performed on it. 

In Pg 7.3 and before, I have successfully used a function with a date parameter such as this simplified version:

CREATE FUNCTION input_date(date) RETURNS INT AS 'SELECT 0;' LANGUAGE 'SQL';

Then, when I calculate a date during execution,
such as:

select input_date('now'::date+'5 years'::interval);

PostgreSQL 7.4 gives error:

ERROR:  function input_date(timestamp without time zone) does not exist

The same function works correctly in PostgreSQL 7.3 and before, and it also works even in 7.4 if I do not try to do
datecalculation, such as: 

select input_date('now');

"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
> In Pg 7.3 and before, I have successfully used a function with a date parameter such as this simplified version:

Perhaps this worked in 7.2 and before, before we tightened the
implicit-casting rules.  It does not work in 7.3.

> CREATE FUNCTION input_date(date) RETURNS INT AS 'SELECT 0;' LANGUAGE 'SQL';

> select input_date('now'::date+'5 years'::interval);

There is not and never has been a date + interval operator.  In all
PG versions, the date constant would get coerced to timestamp or
timestamptz to be added to interval, and the result of the addition
would likewise be of type timestamp or timestamptz.  The reason it
"worked" in old releases is that the result would be silently
down-converted to date to feed to the function.  We no longer think it
a good idea to perform information-losing coercions silently.

If you are intent on having the old behavior you can fool with the
contents of the pg_cast table to make timestamp->date be an implicit
coercion.  But I'd recommend fixing your code instead.  Or create
a date-plus-interval operator that does what you want.

            regards, tom lane