在Postgres例程中添加条件AND运算符

Question

我有一个带有可选参数的Postgres例程，只有在传递了可选参数的情况下，我才想在末尾添加AND运算符。我认为这可能很容易，但我自己无法解决。

create or replace function bincard_all(item_unit_id integer, _institution_id integer, account_id integer, _from_date timestamp default null, _to_date timestamp default null)
-- ...
-- a bunch of SQL queries
-- ...
WHERE (a.institution_id = bincard_all._institution_id OR
(bincard_all._institution_id is null and it.code = 'STO'))
AND a.item_unit_id = bincard_all.item_unit_id
AND a.account_id = bincard_all.account_id
AND a.date BETWEEN _from_date::date AND _to_date::date; -- execute only when the last two parameters are given
end;
$$;

Answer 1

一个典型的解决方案是类似<parameter> IS NULL OR <operation on parameter>的谓词。

...
_from_date IS NULL
OR _to_date IS NULL
OR a.date >= _from_date::date
AND a.date < _to_date::date + '1 day'::interval
...