mercredi 19 mai 2021

Add conditioned AND operator in Postgres routine

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