SQL函数的参数可能为NULL [英] SQL function with parameters that may be NULL

问题描述

我想使用以下函数（在PostgreSQL中）：

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

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

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 = *

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

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?

推荐答案

   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)

这篇关于SQL函数的参数可能为NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！