CODEWITHSUNDEEP
sqlnode.jspostgresqlwhere-clause
vijay
vijay

Reputation: 10997

how to avoid column in 'where' clause if parameter is 'NOT PASSED' postgresql

Running Query :

select * from employee where name = $1 and age = $2 and salary = $3;

Problem Query :

select * from employee where name = $1 and age = $2;

How can I write A Logic That /* If $3 is PASSED Then (and salary = $3 ) */

Note : /* I Cannot check For null or Empty as $3 as not passed then $3 won't be available for check*/

In Node I Am using Like This

        postGresAdaptor.executeQueryWithParameters(QUERY, QUERYPARAMS, function(error, result) {
            if (error) {
                callback(error);
            } else {                    
                data = result.data;                    
            }
        });

Don't Want To Add Logic in node code as there will be lots of queries on the way.

Upvotes: 2

Views: 3835

Answers (2)

wildplasser
wildplasser

Reputation: 44250

It is not very clear what you mean by is passed, but maybe you mean that $3 is an optional parameter, with $3 absent meaning: don't care ?

SELECT *
FROM employee
WHERE name = $1
AND age = $2
AND ( $3 IS NULL OR salary = $3)
   ;

Some data:

CREATE TABLE employee
        ( name varchar NOT NULL PRIMARY KEY
        , age integer
        , salary integer
        );

INSERT INTO employee ( name , age , salary ) VALUES
 ( 'Alice' , 13 , 3 )
,( 'Bob' , 11 , 5 )
,( 'Charlotte' , 15 , 9 )
,( 'David' , 17 , 10 )
        ;

The same as a prepared query:

PREPARE qry (text, integer , integer) AS
SELECT *
FROM employee
WHERE name = $1
AND age = $2
AND ( $3 IS NULL OR salary = $3)
   ;

    -- and call the prepared statement:
EXECUTE qry ('Alice', 13, 3);
EXECUTE qry ('Alice', 13, NULL);

Output:

CREATE TABLE
INSERT 0 4
PREPARE
 name  | age | salary 
-------+-----+--------
 Alice |  13 |      3
(1 row)

 name  | age | salary 
-------+-----+--------
 Alice |  13 |      3
(1 row)

Upvotes: 8

Houari
Houari

Reputation: 5641

You can use default argument type something like:

CREATE OR REPLACE FUNCTION _my_function(_name character varying, _age integer, _salary integer default NULL)
  RETURNS character varying AS
$BODY$
    begin

    if (_salary IS NULL) then

       select * from employee where name = _name and age = _age ;
    else
       select * from employee where name = _name and age = _age and salary =  _salary;
    end IF;
        return 'OK';

end;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Upvotes: 0

Related Questions