Conditional WHERE in plpgsql function

Question

I'm writing function in plpgsql and stuck with following question: my function has parameter service_id_input and query. I want to create additional clause in WHERE section if this variable is not null. For example

SELECT
 MIN(time_from) AS min_0,
 MAX(time_to) AS max_0,
 CEIL((MAX(time_to) * 60 - MIN(time_from) * 60) / reception_duration_input) AS diff_0,
 user_id AS user_id_0,
 workingtimetable.date AS date_0
FROM
 workingtimetable
WHERE
 -- IF service_id_input is not null then add another restriction
 department_id = department_id_input AND date >= NOW() :: DATE
GROUP BY user_id, date
ORDER BY date ASC;

How can I do it?
Full code of function:

CREATE OR REPLACE FUNCTION find_days_business(IN reception_duration_input INTEGER, IN department_id_input BIGINT, IN service_id_input BIGINT)
 RETURNS TABLE(avail_records NUMERIC, busy_records BIGINT, percentage NUMERIC, day_date DATE) AS
 $BODY$
 DECLARE
 BEGIN
 CREATE TEMP TABLE sum_of_avail_records ON COMMIT DROP AS
 SELECT
 SUM(diff_0) AS sum_0,
 date_0
 FROM (
 SELECT
 MIN(time_from) AS min_0,
 MAX(time_to) AS max_0,
 CEIL((MAX(time_to) * 60 - MIN(time_from) * 60) / reception_duration_input) AS diff_0,
 user_id AS user_id_0,
 workingtimetable.date AS date_0
 FROM workingtimetable
 WHERE
 -- IF service_id_input is not null then add another restriction
 department_id = department_id_input AND date >= NOW() :: DATE
 GROUP BY user_id, date
 ORDER BY date ASC
 ) AS perday
 GROUP BY date_0;
 CREATE TEMP TABLE num_of_busy_records ON COMMIT DROP AS
 SELECT
 COUNT(appointments_book_records.id) AS count_1,
 appointments_book_days.date AS date_1
 FROM appointments_book_records
 INNER JOIN appointments_book_days ON appointments_book_records.appointment_day_id = appointments_book_days.id
 WHERE appointments_book_days.date >= NOW() :: DATE
 GROUP BY appointments_book_days.date
 ORDER BY appointments_book_days.date;
 RETURN QUERY
 SELECT
 sum_0,
 count_1,
 (count_1 / sum_0 * 100),
 date_0
 FROM sum_of_avail_records
 LEFT OUTER JOIN num_of_busy_records ON date_0 = date_1;
END;$BODY$
LANGUAGE plpgsql;

• ypercubeᵀᴹ: AND (service_id_input IS NULL OR (<another restriction>))

Answer 1

@Ypercube's comment answers your core question.

Your function is also slower and more complicated than necessary:

CREATE OR REPLACE FUNCTION find_days_business(_reception_duration int
 , _department_id bigint
 , _service_id bigint)
 RETURNS TABLE (avail_records numeric
 , busy_records bigint
 , percentage numeric
 , day_date date)
 LANGUAGE plpgsql AS
$func$
BEGIN
 RETURN QUERY
 SELECT s.sum_diff, n.ct, ((n.ct * 100) / s.sum_diff ), s.date
 FROM (
 SELECT p.date, sum(p.diff) AS sum_diff
 FROM (
 SELECT w.date
 , ceil(((max(w.time_to) - min(w.time_from)) * 60) / _reception_duration) AS diff
 FROM workingtimetable w
 WHERE w.date >= now()::date
 AND w.department_id = _department_id
 -- another restriction IF _service_id is not null:
 AND (_service_id IS NULL OR <another restriction>)
 GROUP BY w.user_id, w.date
 ) p -- perday
 GROUP BY p.date
 ) s -- sum_of_avail_records
 LEFT JOIN (
 SELECT d.date, count(*) AS ct
 FROM appointments_book_records r
 JOIN appointments_book_days d ON r.appointment_day_id = d.id
 WHERE d.date >= CURRENT_DATE
 GROUP BY d.date
 ) n USING (date) -- num_of_busy_records 
 ORDER BY s.date;
END
$func$;

Major points

• Temporary tables are more expensive and not necessary here. Simple subqueries do the job.

• Don't include columns in the SELECT list that you don't need for anything. Dead weight. (You also don't need to include GROUP BY expressions in the SELECT list.)

• Don't ORDER BY in subqueries. While Postgres typically keeps the order in the outer SELECT for simple queries, this is completely arbitrary and not reliable. There is no provision in the SQL standard that would require propagation of ORDER BY in subqueries to the outer query.

If your are looking for fully dynamic query predicates or arbitrary dynamic query elements with optimized query plans, you'll have to use dynamic SQL and EXECUTE:

• PostgresSQL dynamic execute with argument values in array

• Robust approach for building SQL queries programmatically