From: | Glenn Pierce <glennpierce(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Passing array of range literals |
Date: | 2014-03-21 15:55:02 |
Message-ID: | CAM5ipV8NF3WzUYN3udXrNsUoHUNPv-d8P4U15G0HQ78j28J5fA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi
I am try to create a function that returns true if a timestamp is within
working hours.
The function will take the following parameters.
1, timestamp - The timestamp I want to check
2, days_of_week - An array of integers that tells us what days are work
days.
3, time_ranges - An array of my custom timerange type. Allows one to pass
an array of hours in a day that are not work hours.
3, date_ranges - An array of daterange types. Allows one to pass say date
ranges of school holidays.
Something like
CREATE TYPE timerange AS RANGE (
subtype = TIME with time zone
);
CREATE OR REPLACE FUNCTION check_within_working_hours(ts timestamptz,
days_of_week integer[], time_ranges timerange[], date_ranges daterange[])
RETURNS BOOLEAN AS $$
DECLARE passed BOOLEAN;
BEGIN
SELECT extract(dow from $1) = ANY (days_of_week) INTO passed;
IF passed THEN
RETURN passed;
END IF;
return 'f';
END;
$$ LANGUAGE plpgsql;
I have a large table with timestamp ts and double value. I was going to
call the function above like
SELECT * FROM sensor_values WHERE check_within_working_hours(ts,
'{1,2,3}'::integer[], '{}'::timerange[], NULL) LIMIT 10;
This works but I have a few problems.
I cannot work out how to pass a literal for the array of timerange types.
'{(15:11:21, 18:11:21)}'::timerange[] does not work for example.
Also I can not pass NULL for this parameter I get
ERROR: function check_within_working_hours(timestamp with time zone,
integer[], unknown, unknown) is not unique
Once I can pass the parameters I need the sql to check my passed timestamp
is within the array of timeranges or dateranges.
Does anyone know what the most efficient means to achieve that is ?
Also should I investigate creating this as a c function or will it be ok
performance wise?
Thanks for any advice
From | Date | Subject | |
---|---|---|---|
Next Message | Rich Shepard | 2014-03-21 16:34:10 | Upgrade: 9.0.5->9.4 |
Previous Message | Sergey Konoplev | 2014-03-21 15:21:12 | Re: Dead rows not getting removed during vacuum |