I have a Postgres routine with optional parameters and I want to add the AND operator at the end only if the optional parameters are passed. I think this could be easy, but couldn't figure it out by myself.
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;
$$;
Aucun commentaire:
Enregistrer un commentaire