Conditional WHERE in plpgsql function

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;

CR

@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 )
         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

StackExchange Database Administrators Q#142948, answer score: 2