[7.3.x] function does not exist ... ?

Marc G. Fournier wrote:

'k, this doesn't look right, but it could be that I'm overlooking
something ...

The function I created:

CREATE FUNCTION month_trunc (timestamp without time zone) RETURNS timestamp without time zone
AS 'SELECT date_trunc(''month'', $1 )'
LANGUAGE sql IMMUTABLE;

The query that fails:

ams=# select * from traffic_logs where month_trunc(runtime) = month_trunc(now());
ERROR: Function month_trunc(timestamp with time zone) does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts

now return a timestamp with time zone and your function
take a timestamp without time zone.
^^^^^^^

Regards
Gaetano Mendola

Marc G. Fournier wrote:

'k, this doesn't look right, but it could be that I'm overlooking
something ...

The function I created:

CREATE FUNCTION month_trunc (timestamp without time zone) RETURNS timestamp without time zone
AS 'SELECT date_trunc(''month'', $1 )'
LANGUAGE sql IMMUTABLE;

The query that fails:

ams=# select * from traffic_logs where month_trunc(runtime) = month_trunc(now());
ERROR: Function month_trunc(timestamp with time zone) does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts

The query that succeeds:

ams=# explain select * from traffic_logs where month_trunc(runtime) = '2003-10-01';
QUERY PLAN
-------------------------------------------------------------------------------------------
Index Scan using tl_month on traffic_logs (cost=0.00..30751.90 rows=8211 width=36)
Index Cond: (month_trunc(runtime) = '2003-10-01 00:00:00'::timestamp without time zone)
(2 rows)

I haven't mis-spelt anything that I can see ... is this something that is
known not to be doable?

Try casting now() to timestamp without time zone?

cheers

andrew

On Mon, 10 Nov 2003, Gaetano Mendola wrote:

Marc G. Fournier wrote:

'k, this doesn't look right, but it could be that I'm overlooking
something ...

The function I created:

CREATE FUNCTION month_trunc (timestamp without time zone) RETURNS timestamp without time zone
AS 'SELECT date_trunc(''month'', $1 )'
LANGUAGE sql IMMUTABLE;

The query that fails:

ams=# select * from traffic_logs where month_trunc(runtime) = month_trunc(now());
ERROR: Function month_trunc(timestamp with time zone) does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts

now return a timestamp with time zone and your function
take a timestamp without time zone.
^^^^^^^

d'oh, I knew I was mis-reading something there ... thanks