0

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 timestamps.

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!

6
  • So if I understand this correctly, both recurrence and recurrence_exceptions are stored as text. So are they stored using the same format? Commented Sep 20, 2016 at 16:25
  • recurrence is text and recurrence_exceptions is a timestamp[] array. However, I tried returning recurrence as timestamp as well with no luck. Commented Sep 20, 2016 at 16:57
  • You have to give us a minimal and complete example. Try to simplify the schema as much as possible and remove lines from the above function that are not related to your problem. Then post those here, along with some insert statements for sample data. Commented Sep 20, 2016 at 17:00
  • @redneb appreciate your help. Turns out this was a bad logic issue on my part -- nothing to do with ANY. Debugging these things is really difficult compared to applications! The tooling just doesn't seem to be there. Any suggestions? Commented Sep 20, 2016 at 17:42
  • So I'm confused, do you still have a problem? Commented Sep 20, 2016 at 18:20

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.