Thread: Passing array of range literals
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
Glenn Pierce wrote
> 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
since NULL can take on any type if you pass it literally you have to specify
the type you need:
NULL::timerange[]
Though I'd suggest passing in an empty array instead:
ARRAY[]::timerange[]
Note this is also the best way to construct the array:
ARRAY['(15:11:21,18:11:21)','(19.11.22,21:12:17]']::timerange[]
Otherwise you need to use double-quotes somewhere. Once you construct an
array as above just cast it to text and let PostgreSQL tell you what the
text representation would look like if you cannot use the ARRAY[] form.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Passing-array-of-range-literals-tp5797031p5797062.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.