Passing array of range literals

Started by Glenn Pierceabout 12 years ago2 messagesgeneral
Jump to latest
#1Glenn Pierce
glennpierce@gmail.com

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

#2David G. Johnston
david.g.johnston@gmail.com
In reply to: Glenn Pierce (#1)
Re: Passing array of range literals

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general