postgresql 如果参数为 NULL，如何从 WHERE 子句中删除条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23386078/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to remove conditions from WHERE clause if parameters are NULL
提问by user3339988
I'm passing 2 parameters to a PL/pgSQL function. Here's the query:
我将 2 个参数传递给 PL/pgSQL 函数。这是查询：
SELECT *
FROM table
WHERE col1 = param1
AND col2 = param2
Both parameters can be NULL, in which case the respective expression should be removed from the WHEREclause.
两个参数都可以为 NULL，在这种情况下，应从WHERE子句中删除相应的表达式。
How can I do that? With IFconditions?
我怎样才能做到这一点？随着IF条件是什么？
回答by frlan
Maybe this is doing the trick:
也许这就是诀窍：
SELECT *
FROM table
WHERE col1 = param1
AND (param2 is null or col2 = param2);
This is not removing the AND condition, but should make the unimportant in case of param2 is null. So not clearly answering your question but going around... ;)
这不是删除 AND 条件，而是在 param2 为空的情况下使不重要。所以没有清楚地回答你的问题，而是四处走动......;）
回答by Erwin Brandstetter
A simple (param1 IS NULL OR col1 = param1)takes care of this, as has been answered already.
(param1 IS NULL OR col1 = param1)正如已经回答的那样，一个简单的方法可以解决这个问题。
To actually removeany or all NULL conditions, you need dynamic SQL. You can build your statement in the client conditionally or you can create a plpgsql (or any other procedural language) function to take care of it. This approach may result in superior query plans when dealing with complex queries.
要实际删除任何或所有 NULL 条件，您需要动态 SQL。您可以有条件地在客户端中构建您的语句，或者您可以创建一个 plpgsql（或任何其他过程语言）函数来处理它。在处理复杂查询时，这种方法可能会产生更好的查询计划。
PL/pgSQLfunction
PL/pgSQL函数
The tricky parts are:
棘手的部分是：
- to deal with NULL values and empty strings properly in string concatenation
- to deal with variable data types while avoiding possible SQL injection
- 在字符串连接中正确处理 NULL 值和空字符串
- 处理可变数据类型，同时避免可能的 SQL 注入
CREATE OR REPLACE FUNCTION f_conditional_where(_param1 int = NULL
, _param2 text = NULL
, _param3 date = NULL)
RETURNS SETOF tbl AS
$func$
DECLARE
_where text :=
concat_ws(' AND '
, CASE WHEN _param1 IS NOT NULL THEN 'col1 = ' END
, CASE WHEN _param2 IS NOT NULL THEN 'col2 = ' END
, CASE WHEN _param3 IS NOT NULL THEN 'col3 = ' END);
_sql text := 'SELECT * FROM tbl';
BEGIN
IF _where <> '' THEN
_sql := _sql || ' WHERE ' || _where;
END IF;
-- debug output
RAISE NOTICE '
_sql: |%|
_where: |%|', _sql, _where;
-- execute
RETURN QUERY EXECUTE _sql
USING , , ;
END
$func$ LANGUAGE plpgsql;
Three examples for function call:
函数调用的三个例子：
SELECT * FROM f_conditional_where();
SELECT * FROM f_conditional_where(1, 'foo');
SELECT * FROM f_conditional_where(_param3 := '2012-01-01', _param2 := 'foo');
You need to have a basic understanding of plpgsql for this. You'll find plenty of examples with ample explanation in the plpgsqltag.
为此，您需要对 plpgsql 有基本的了解。您会在plpgsql标签中找到大量带有充分解释的示例。
回答by IMSoP
The simplest (though probably not the most efficient) way is to handle the null inside the SQL statement itself, e.g.:
最简单（虽然可能不是最有效）的方法是处理 SQL 语句本身内部的空值，例如：
SELECT *
FROM table
WHERE col1 = param1
AND (param2 IS NULL OR col2 = param2)
This by-passes the clause for all rows if the parameter is null.
如果参数为空，这将绕过所有行的子句。
Or with this trick:
或者用这个技巧：
SELECT *
FROM table
WHERE col1 = param1
AND col2 = COALESCE(param2, col2)
When param2is NULL, the condition is equivalent to col2 = col2, which will always be true as long as col2doesn't itself contain NULLvalues.
当param2is 时NULL，条件等价于col2 = col2，只要col2它本身不包含NULL值，它就始终为真。
A quick test using hard-coded values rather than a function parameter gave me the same query plan for both approaches - the ORand COALESCEparts are seemingly optimised away before planning the query, so it's as though the second ANDwere indeed being conditionally removed.
使用硬编码值而不是函数参数的快速测试为我提供了两种方法相同的查询计划 -OR和COALESCE部分似乎在计划查询之前被优化掉了，所以好像第二AND个确实被有条件地删除了。
