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;

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