| 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: | Whole Thread | Raw Message | 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 |