SQL function with parameters that may be NULL

postgresql, sql, stored-procedures

Solution

   AND (userName is null or u.name = userName)
   AND (processName is null or p.name = processName)
   AND (dTimeIn is null or i.dateTimeIn > dTimeIn)
   AND (dTimeOut is null or i.dateTimeOut < dTimeOut)
   AND (macAddress is null or c.mac_address = macAddress)
   AND (lName is null or l.name = lName)
   AND (subjectName is null or d.name = subjectName)

Problem

I want to use the following function (in PostgreSQL): ``` create type holder as (userName varchar(50), processName varchar(50), dateTime timestamp, macAddress varchar(30), labName varchar(50), subjectName varchar(50)); create or replace function returnData( uName varchar(50), pName varchar(50), dTimeIn timestamp, dTimeOut timestamp, macAddress MACADDR, lName varchar(50), subjectName varchar(50) ) returns setof holder as $$ declare r holder%rowtype; begin for r in (SELECT DISTINCT u.name, p.process_name, i.date_time, c.mac_address, l.lab_name, d.subject_name FROM user u, session s, init i, process p, computer c, laboratory l, class a, subject d, occur_in o WHERE i.process_id = p.process_id AND i.session_id = s.session_id AND s.user_id = u.user_id AND s.comp_id = c.comp_id AND c.lab_id = l.lab_id AND l.lab_id = o.lab_id AND o.class_id = a.class_id AND a.subject_id = d.subject_id AND p.blocked = true --important part now AND u.name = userName AND p.name = processName AND i.dateTimeIn > dTimeIn AND i.dateTimeOut < dTimeOut AND c.mac_address = macAddress AND l.name = lName AND d.name = subjectName ) loop return next r; end loop; return; end; $$ language 'plpgsql'; ``` I may have some typos as I translated this to english, but actual function is working, as long as I pass valid parameters.But my user may not want to filter results by user name, for example. Or he may choose to bring data after a date but do not specify a finish date. I could do somethig like ``` ... IF (userName IS NULL AND pName IS NULL AND ...) -- query without filtering user name nor process name, -- filtering or not the other parameters IF (userName IS NOT NULL AND pName IS NULL AND ...) --filter user name, don't filter process name, -- filtering or not the other parameters ... ``` And so on. But, if I'm not wrong, I'd have to write 49 querys as I have 7 parameters which may or may not be filtered. I could do that, but if possible I'd like to simplify my query by doing something like ``` SELECT ... FROM ... WHERE ... AND userName = * ``` where * would be the value in the parameter uName if user do not want to filter user name. Is it possible to do something like this? Is there any better way to do this? And what to do with the two timestamps?

Original source