Passing array of range literals

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

Responses

Browse pgsql-general by date

  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