From: | M Q <thekaib(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | "where x between y and z" for timestamp data types |
Date: | 2012-08-12 18:52:22 |
Message-ID: | CAGnP9ZgE8KFmO1hx6-Vjy6HCftKL=aBAgwnZumPBj9a8+Y2PZA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Davis | 2012-08-12 21:07:23 | Re: "where x between y and z" for timestamp data types |
Previous Message | Csanyi Pal | 2012-08-11 19:29:43 | Re: To get a Table or View like a Calendar with dates |