参数可能为NULL的SQL函数

Question

我想使用以下函数（在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';

我将其翻译成英文时可能会有一些错别字，但只要传递有效参数，实际功能就可以正常工作。但是，例如，我的用户可能不想按用户名过滤结果。或者，他可以选择在日期之后携带数据，但不指定结束日期。我可以做一些像

...   
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
...

等等。但是，如果我没记错的话，我必须编写49个查询，因为我有7个可能会或可能不会被过滤的参数。我可以这样做，但是如果可能的话，我想通过做类似的事情来简化查询

SELECT ...
FROM ...
WHERE ...
AND userName = *

如果用户不想过滤用户名，则*是参数uName中的值。有可能做这样的事情吗？有没有更好的方法可以做到这一点？以及如何处理这两个时间戳？

Answer 1

   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)