title: How can i use SQL where clause with multiple parameters
body: <p>I have a <code>TABLE</code> with 9 fields and I created a report in Ireport with 9 parameters from this <code>TABLE</code> but some times users fill some of parameters , not all of them.
  so , as far as you know I should check all of them with each other . <code>IF is null</code> do this and <code>IF is not null</code> do that.
  so , please help and tell to me how can I handle it?</p>
  
  <pre><code>+-------+-------+-------+-------+-------+-------+-------+-------+-------+
  | Col_1 | Col_2 | Col_3 | Col_4 | Col_5 | Col_6 | Col_7 | Col_8 | Col_9 |
  +-------+-------+-------+-------+-------+-------+-------+-------+-------+
  | x     | y     | 3o    | xT    | y1    | k32   | xY    | L     | 3q    |
  | s     | u     | 4t    | xO    | y3    | j34   | xO    | K     | 2a    |
  | h     | z     | 33r   | xH    | y3    | h36   | xT    | y     | 6s    |
  | t     | t     | 14r   | xF    | y5    | t31   | xP    | J     | 1d    |
  | m     | m     | 77w   | xS    | y7    | o30   | xJ    | O     | 0f    |
  +-------+-------+-------+-------+-------+-------+-------+-------+-------+
  </code></pre>
  
  <p>and my query is this</p>
  
  <pre><code>CREATE FUNCTION public.rep_staff(
      parCol1 text,
      parCol2 text,
      parCol3 text,
      parCol4 text,
      parCol5 text,
      parCol6 text,
      parCol7 text,
      parCol8 text,
      parCol9 text,
      OUT Col_1 character varying,
      OUT Col_2 character varying,
      OUT Col_3 character varying,
      OUT Col_4 character varying,
      OUT Col_5 character varying,
      OUT Col_6 character varying,
      OUT Col_7 character varying,
      OUT Col_8 character varying,
      OUT Col_9 character varying)
  RETURNS SETOF record 
      LANGUAGE 'plpgsql'
  AS $BODY$
  
  DECLARE
      parCol1_par text;
      parCol2_par text;
      parCol3_par text;
      parCol4_par text;
      parCol5_par text;
      parCol6_par text;
      parCol7_par text;
      parCol8_par text;
      parCol9_par text;
  BEGIN
      parCol1_par = parCol1 ;
      parCol2_par = parCol2 ;
      parCol3_par = parCol3 ;
      parCol4_par = parCol4 ;
      parCol5_par = parCol5 ;
      parCol6_par = parCol6 ;
      parCol7_par = parCol7 ;
      parCol8_par = parCol8 ;
      parCol9_par = parCol9 ;
  
      IF ( parCol1_par is NOT  null AND 
               parCol2_par is NOT  null AND 
               parCol3_par is NOT  null AND 
               parCol4_par is NOT  null AND 
               parCol5_par is NOT  null AND 
               parCol6_par is NOT  null AND 
               parCol7_par is NOT  null AND 
               parCol8_par is NOT  null AND 
               parCol9_par is NOT null)
          THEN
              RETURN QUERY
              SELECT
                  *
              FROM
                  "public"."TABLE" T
              WHERE T.parCol1 = parCol1_par AND
                    T.parCol2 = parCol2_par AND
                    T.parCol3 = parCol3_par AND
                    T.parCol4 = parCol4_par AND
                    T.parCol5 = parCol5_par AND
                    T.parCol6 = parCol6_par AND
                    T.parCol7 = parCol7_par AND
                    T.parCol8 = parCol8_par AND
                    T.parCol9 = parCol9_par;
  
          ELSE IF ( parCol1_par is null AND 
               parCol2_par is NOT  null AND 
               parCol3_par is NOT  null AND 
               parCol4_par is NOT  null AND 
               parCol5_par is NOT  null AND 
               parCol6_par is NOT  null AND 
               parCol7_par is NOT  null AND 
               parCol8_par is NOT  null AND 
               parCol9_par is NOT null)
          THEN
              RETURN QUERY
              SELECT
                  *
              FROM
                  "public"."TABLE" T
              WHERE T.parCol2 = parCol2_par AND
                    T.parCol3 = parCol3_par AND
                    T.parCol4 = parCol4_par AND
                    T.parCol5 = parCol5_par AND
                    T.parCol6 = parCol6_par AND
                    T.parCol7 = parCol7_par AND
                    T.parCol8 = parCol8_par AND
                    T.parCol9 = parCol9_par;
  
          ELSE IF (
                    .
                    .
                    .
                    .
                    .
                    .
                    .
          ELSE IF (
          ELSE IF (
          ELSE IF (
  
          END IF;
  END
  
  $BODY$;
  </code></pre>