SQL function with parameters that may be NULL

Question

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?

You might also consider using OUTER joins here.

vyegorov
– vyegorov

2012-10-26 07:49:24 +00:00
Commented Oct 26, 2012 at 7:49 — vyegorov
– vyegorov, Commented Oct 26, 2012 at 7:49

Clodoaldo Neto · Accepted Answer · 2012-10-25 23:11:47Z

4

   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)

answered Oct 25, 2012 at 23:11

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jRicardo Over a year ago

I guess my question was not clear. I tested your answer with null as userName, and it returned me nothing. When passing null, it should return me all names.

Clodoaldo Neto Over a year ago

@user1531978 That's how I understood it. I made a small but significant edit after posting. Did you get the first one or the current one?

Clodoaldo Neto Over a year ago

@user1531978 What I answered is a very common pattern to deal with that problem.

Collectives™ on Stack Overflow

SQL function with parameters that may be NULL

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related