0

I have a Postgres function where I need to dynamically add a conditional parameter if it was passed into the function. 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
            -- NEED TO CONDITIONALLY ADD "WHERE staff_id = for_staff_id" 
            -- if for_staff_id is NOT NULL
            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;
                CONTINUE WHEN (recurrence::date < range_start AND recurrence::date > range_end) 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$;

If for_staff_id is passed into the function, I need to add it as a conditional (WHERE staff_id = for_staff_id) to the query that's located in the FOR...IN loop. I've tried doing an IF/ELSE within the FOR...IN, but I'm getting a syntax error.

Is there a better way to do this?

Thanks!

Note that I'm running Postgres 9.5.

1 Answer 1

2

Just add the following

... AND (for_staff_id IS NULL OR staff_id = for_staff_id)

or this

... AND coalesce(staff_id = for_staff_id, true)

in the WHERE clause of the SELECT statement.

Sign up to request clarification or add additional context in comments.

1 Comment

coalesce(staff_id = for_staff_id, true) was the ticket! Thanks, @redneb :D

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.