Обсуждение: [7.3.x] function does not exist ... ?
'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 satisfiesthe 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'::timestampwithout time zone) (2 rows) I haven't mis-spelt anything that I can see ... is this something that is known not to be doable?
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