Hi,
I'm having trouble creating a function with a "where x between y and z" for timestamp data types.
If I hardcode the values for y and z, my function works fine and returns quickly (~80ms). If I parameterize y and z (i.e. use $1, $2), then the function doesn't seem to return (killed query after waiting > 30 seconds)
Example:
I have two tables with timestamp data.
The hardcoded solution looks like this:
CREATE OR REPLACE FUNCTION time_test(IN timestamp without time zone, IN timestamp without time zone)
RETURNS TABLE(v1 double precision, v2 double precision, ti1 timestamp without time zone, ti2 timestamp without time zone) AS
$BODY$
BEGIN
RETURN QUERY
SELECT t1.value1, t2.value2, $1, $2
FROM
MyTable1 as t1 inner join MyTable2 as t2 on t1.date = t2.date
WHERE t1.date between '2010-06-01 15:10:20' and '2010-06-01 15:10:20' ;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
select * from time_test('2010-06-01 15:10:20', '2010-06-01 15:10:20'); /* arguments here aren't really used internally by where clause */
The paramterized solution looks like this (Same as above but just using $1 and $2 in where clause):
CREATE OR REPLACE FUNCTION time_test(IN timestamp without time zone, IN timestamp without time zone)
RETURNS TABLE(v1 double precision, v2 double precision, ti1 timestamp without time zone, ti2 timestamp without time zone) AS
$BODY$
BEGIN
RETURN QUERY
SELECT t1.value1, t2.value2, $1, $2
FROM
MyTable1 as t1 inner join MyTable2 as t2 on t1.date = t2.date
WHERE t1.date between $1 and $2;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
select * from time_test('2010-06-01 15:10:20', '2010-06-01 15:10:20'); /* arguments here should produce same result as hardcoded solution */
What am I not understanding?
Any help would be appreciated.
Thanks,
Kaib