jdixon04 jdixon04 - 2 months ago 8
SQL Question

postgres: dynamic conditional in function query

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.

Answer

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.