如果参数是'NOT PASSED' postgresql 如何避免'where'子句中的列

问题描述

运行查询:

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

问题查询:

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

我怎样才能写出一个逻辑/* 如果 $3 是 PASSED Then (andsalary = $3 ) */

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

注意:/* 我无法检查对于null或Empty作为$3作为未通过然后$3> 将不可用于check*/

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

在 Node 中我使用的是这样的

        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.

推荐答案

你说的通过不是很清楚，但也许你的意思是 $3 是一个可选参数，没有 $3 表示:don't care ?

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);

输出:

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

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

这篇关于如果参数是'NOT PASSED' postgresql 如何避免'where'子句中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！