I have a Postgres function where I need to check whether or not a particular value is in an array of timestamps. Here's the function:
CREATE OR REPLACE FUNCTION public.get_appointments(
for_business_id INTEGER,
range_start DATE,
range_end DATE,
for_staff_id INTEGER
)
RETURNS SETOF appointment
LANGUAGE plpgsql STABLE
AS $function$
DECLARE
appointment appointment;
recurrence TIMESTAMP;
appointment_length INTERVAL;
parent_id UUID;
BEGIN
FOR appointment IN
SELECT *
FROM appointment
WHERE business_id = for_business_id
AND (
recurrence_pattern IS NOT NULL
OR (
recurrence_pattern IS NULL
AND starts_at BETWEEN range_start AND range_end
)
)
LOOP
IF appointment.recurrence_pattern IS NULL THEN
RETURN NEXT appointment;
CONTINUE;
END IF;
appointment_length := appointment.ends_at - appointment.starts_at;
parent_id := appointment.id;
FOR recurrence IN
SELECT *
FROM generate_recurrences(
appointment.recurrence_pattern,
appointment.starts_at,
range_start,
range_end
)
LOOP
EXIT WHEN recurrence::date > range_end;
-- THIS IS THE LINE IN QUESTION
CONTINUE WHEN recurrence::date < range_start OR recurrence = ANY(appointment.recurrence_exceptions);
appointment.id := uuid_generate_v5(uuid_nil(), parent_id::varchar || recurrence::varchar);
appointment.parent_id := parent_id;
appointment.starts_at := recurrence;
appointment.ends_at := recurrence + appointment_length;
appointment.recurrence_pattern := appointment.recurrence_pattern;
appointment.recurrence_exceptions := NULL;
appointment.is_recurrence := true;
RETURN NEXT appointment;
END LOOP;
END LOOP;
RETURN;
END;
$function$;
You'll see that just after the second LOOP
statement, there's a CONTINUE
statement. I want to skip that iteration of the loop if the recurrence
(this is a timestamp, but in text
format) variable is either out of range OR if it's listed as part of the appointment
's recurrence_exceptions
array. The recurrence_exceptions
array contains timestamp
s.
The idea is that if the appointment is listed as an exception, it is not returned. Unfortunately, no matter what I do, it seems that the ANY
operator isn't working as expected. To test this, I took one of the values from the recurrence_exception
array and changed the CONTINUE
statement to:
CONTINUE WHEN recurrence::date < range_start OR recurrence = '2016-09-20 18:07:26';
This did not return that recurrence as expected.
Am I using this properly?
Thank you!
recurrence
andrecurrence_exceptions
are stored as text. So are they stored using the same format?recurrence
istext
andrecurrence_exceptions
is atimestamp[]
array. However, I tried returningrecurrence
astimestamp
as well with no luck.ANY
. Debugging these things is really difficult compared to applications! The tooling just doesn't seem to be there. Any suggestions?