postgresql 用不同的参数测试函数中的空值

Question

提问by milovanderlinden

I have a Postgres function:

我有一个 Postgres 函数：

create function myfunction(integer, text, text, text, text, text, text) RETURNS 
table(id int, match text, score int, nr int, nr_extra character varying, info character varying, postcode character varying,
street character varying, place character varying, country character varying, the_geom geometry)
AS $$
BEGIN

return query (select a.id, 'address' as match, 1 as score, a.ad_nr, a.ad_nr_extra,a.ad_info,a.ad_postcode, s.name as street, p.name place , c.name country, a.wkb_geometry as wkb_geometry from "Addresses" a 
    left join "Streets" s on a.street_id = s.id 
        left join "Places" p on s.place_id = p.id 
            left join "Countries" c on p.country_id = c.id 
            where c.name =  
                and p.name = 
                    and s.name = 
                    and a.ad_nr =  
                    and a.ad_nr_extra = 
                    and a.ad_info = 
                    and ad_postcode = );
END;
$$
LANGUAGE plpgsql;

This function fails to give the right result when one or more of the variables entered are NULL because ad_postcode = NULLwill fail.

当输入的一个或多个变量为 NULL 时，此函数无法给出正确的结果，因为ad_postcode = NULL会失败。

What can I do to test for NULL inside the query?

我可以做些什么来测试查询中的 NULL？

Answer 1

回答by Erwin Brandstetter

I disagree with some of the advice in other answers. This can be done with plpgsql and I think it is mostly far superiorto assembling queries in a client application. It is faster and cleaner and the app only sends the bare minimum across the wire in requests. SQL statements are saved inside the database, which makes it easier to maintain - unless you want to collect all business logic in the client application, this depends on the general architecture.

我不同意其他答案中的一些建议。这可以通过 plpgsql 完成，我认为它比在客户端应用程序中组装查询要好得多。它更快更干净，应用程序仅在请求中通过网络发送最低限度。SQL 语句保存在数据库内部，这样更易于维护——除非您想收集客户端应用程序中的所有业务逻辑，否则这取决于通用架构。

General advice

一般建议

You don't need parentheses around the SELECTwith RETURN QUERY
Neveruse nameand idas column names. They are not descriptive and when you join a bunch of tables (like you have to a lotin a relational database), you end up with several columns all named nameor id. Duh.
Please format your SQL properly, at least when asking public questions. But do it privately as well, for your own good.

你不需要周围的括号SELECT用RETURN QUERY
永远不要使用name和id作为列名。它们不是描述性的，当你加入一堆表时（就像你必须a lot在关系数据库中一样），你最终会得到几个名为nameor 的列id。呃。
请正确格式化您的 SQL，至少在询问公共问题时是这样。但是，为了您自己的利益，也请私下进行。

PL/pgSQL function

PL/pgSQL 函数

CREATE OR REPLACE FUNCTION func(
      _ad_nr       int  = NULL
    , _ad_nr_extra text = NULL
    , _ad_info     text = NULL
    , _ad_postcode text = NULL
    , _sname       text = NULL
    , _pname       text = NULL
    , _cname       text = NULL)
  RETURNS TABLE(id int, match text, score int, nr int, nr_extra text
              , info text, postcode text, street text, place text
              , country text, the_geom geometry) AS
$func$
BEGIN

-- RAISE NOTICE '%', -- for debugging
RETURN QUERY EXECUTE concat(
$$SELECT a.id, 'address'::text, 1 AS score, a.ad_nr, a.ad_nr_extra
     , a.ad_info, a.ad_postcode$$

, CASE WHEN (_sname, _pname, _cname) IS NULL THEN ', NULL::text' ELSE ', s.name' END  -- street
, CASE WHEN (_pname, _cname) IS NULL         THEN ', NULL::text' ELSE ', p.name' END  -- place
, CASE WHEN _cname IS NULL                   THEN ', NULL::text' ELSE ', c.name' END  -- country
, ', a.wkb_geometry'

, concat_ws('
JOIN   '
, '
FROM   "Addresses" a'
, CASE WHEN NOT (_sname, _pname, _cname) IS NULL THEN '"Streets"   s ON s.id = a.street_id' END
, CASE WHEN NOT (_pname, _cname) IS NULL         THEN '"Places"    p ON p.id = s.place_id' END
, CASE WHEN _cname IS NOT NULL                   THEN '"Countries" c ON c.id = p.country_id' END
)

, concat_ws('
AND    '
   , '
WHERE  TRUE'
   , CASE WHEN  IS NOT NULL THEN 'a.ad_nr = ' END
   , CASE WHEN  IS NOT NULL THEN 'a.ad_nr_extra = ' END
   , CASE WHEN  IS NOT NULL THEN 'a.ad_info = ' END
   , CASE WHEN  IS NOT NULL THEN 'a.ad_postcode = ' END
   , CASE WHEN  IS NOT NULL THEN 's.name = ' END
   , CASE WHEN  IS NOT NULL THEN 'p.name = ' END
   , CASE WHEN  IS NOT NULL THEN 'c.name = ' END
)
)
USING , , , , , , ;

END
$func$ LANGUAGE plpgsql;

Call:

称呼：

SELECT * FROM func(1, '_ad_nr_extra', '_ad_info', '_ad_postcode', '_sname');

SELECT * FROM func(1, _pname := 'foo');

Since I provided default values for all function parameters, you can use positionalnotation, namednotation or mixednotation in the function call. More in this related answer:
Functions with variable number of input parameters
For more explanation on the basics of dynamic SQL, refer to this related answer:
Refactor a PL/pgSQL function to return the output of various SELECT queries
The concat()function is instrumental for building the string. It was introduced with Postgres 9.1.
The ELSEbranch of a CASEstatement defaults to NULLwhen not present. Simplifies the code.
The USINGclause for EXECUTEmakes SQL injection impossible and allows to use parameter values directly, exactly like prepared statements.
NULLvalues are used to ignore parameters here. They are not actually used to search.

由于我为所有函数参数提供了默认值，因此您可以在函数调用中使用位置符号、命名符号或混合符号。此相关答案中的更多内容：
具有可变数量输入参数的函数
有关动态 SQL 基础知识的更多解释，请参阅此相关答案：
重构 PL/pgSQL 函数以返回各种 SELECT 查询的输出
该concat()函数有助于构建字符串。它是在 Postgres 9.1 中引入的。
语句的ELSE分支在不存在时CASE默认为NULL。简化代码。
该USINGfor子句EXECUTE品牌SQL注入是不可能的，并允许直接使用的参数值，酷似准备语句。
NULL这里的值用于忽略参数。它们实际上并不用于搜索。

Simple SQL function

简单的SQL函数

You coulddo it with a plain SQL function and avoid dynamic SQL. For some cases this may be faster. But I wouldn't expect it in this case. Re-planning the query with or without joins and where conditions is the superior approach. It will give you optimized query plans. Planning cost for a simple query like this is almost negligible.

您可以使用普通的 SQL 函数来完成它并避免使用动态 SQL。对于某些情况，这可能更快。但在这种情况下我不会期望它。重新规划带或不带连接的查询以及 where 条件是更好的方法。它将为您提供优化的查询计划。像这样的简单查询的规划成本几乎可以忽略不计。

CREATE OR REPLACE FUNCTION func_sql(
     _ad_nr       int  = NULL
   , _ad_nr_extra text = NULL
   , _ad_info     text = NULL
   , _ad_postcode text = NULL
   , _sname       text = NULL
   , _pname       text = NULL
   , _cname       text = NULL)
  RETURNS TABLE(id int, match text, score int, nr int, nr_extra text
              , info text, postcode text, street text, place text
              , country text, the_geom geometry) AS 
$func$

SELECT a.id, 'address' AS match, 1 AS score, a.ad_nr, a.ad_nr_extra
     , a.ad_info, a.ad_postcode
     , s.name AS street, p.name AS place
     , c.name AS country, a.wkb_geometry
FROM   "Addresses"      a
LEFT   JOIN "Streets"   s ON s.id = a.street_id
LEFT   JOIN "Places"    p ON p.id = s.place_id
LEFT   JOIN "Countries" c ON c.id = p.country_id
WHERE ( IS NULL OR a.ad_nr = )
AND   ( IS NULL OR a.ad_nr_extra = )
AND   ( IS NULL OR a.ad_info = )
AND   ( IS NULL OR a.ad_postcode = )
AND   ( IS NULL OR s.name = )
AND   ( IS NULL OR p.name = )
AND   ( IS NULL OR c.name = )

$func$ LANGUAGE sql;

Identical call.

一模一样的叫法。

To effectively ignore parameters with NULLvalues:

要有效地忽略具有NULL值的参数：

( IS NULL OR a.ad_nr = )

If you actually want to use NULL values as parameters, use this construct instead:

如果您确实想使用NULL 值作为参数，请改用此构造：

( IS NULL AND a.ad_nr IS NULL OR a.ad_nr = )  -- AND binds before OR

This also allows for indexesto be used.
Also replace all instances of LEFT JOINwith JOIN.

这也允许使用索引。
同时更换的所有实例LEFT JOIN有JOIN。

SQL Fiddlewith simplified demo for all variants.

SQL Fiddle带有所有变体的简化演示。

Answer 2

回答by Igor Romanchenko

You can use

您可以使用

c.name IS NOT DISTINCT FROM

It will return trueif c.nameand $7are equal or both are null.

true如果c.name和$7相等或两者都相等，它将返回null。

Or you can use

或者你可以使用

(c.name =  or  is null )

It will return trueif c.nameand $7are equal or $7is null.

true如果c.name和$7相等或$7为空，它将返回。

Answer 3

回答by bma

If you can modify the query, you could do something like

如果您可以修改查询，则可以执行以下操作

and (ad_postcode =  OR  IS NULL)

Answer 4

回答by Denis de Bernardy

Several things...

几件事...

First, as side note: the semantics of your query might need a revisit. Some of the stuff in your whereclauses might actually belong in your joinclauses, like:

首先，作为旁注：您的查询的语义可能需要重新审视。您where条款中的某些内容实际上可能属于您的join条款，例如：

from ...
left join ... on ... and ...
left join ... on ... and ...

When they don't, you most should probably be using an inner join, rather than a left join.

当他们不这样做时，您最应该使用的是inner join，而不是left join。

Second, there is a is not distinct fromoperator, which can occasionally be handy in place of =. a is not distinct from bis basically equivalent to a = b or a is null and b is null.

其次，有一个is not distinct from运算符，它偶尔可以代替=. a is not distinct from b基本上等价于a = b or a is null and b is null.

Note, however, that is not distinct fromdoes NOTuse an index, whereas =and is nullactually do. You could use (field = $i or $i is null)instead in your particular case, and it will yield the optimal plan if you're using the latest version of Postgres:

但是请注意，这is not distinct from确实不使用索引，而=和is null实际做。您可以(field = $i or $i is null)在您的特定情况下使用，如果您使用的是最新版本的 Postgres，它将产生最佳计划：

https://gist.github.com/ddebernardy/5884267

postgresql 用不同的参数测试函数中的空值

提问by milovanderlinden

回答by Erwin Brandstetter

General advice

一般建议

PL/pgSQL function

PL/pgSQL 函数

Simple SQL function

简单的SQL函数

回答by Igor Romanchenko

回答by bma

回答by Denis de Bernardy

相关推荐

最近更新

标签

postgresql 用不同的参数测试函数中的空值

提问by milovanderlinden

回答by Erwin Brandstetter

General advice

一般建议

PL/pgSQL function

PL/pgSQL 函数

Simple SQL function

简单的SQL函数

回答by Igor Romanchenko

回答by bma

回答by Denis de Bernardy

相关推荐

org.postgresql.util.PSQLException：致命：没有主机的 pg_hba.conf 条目

postgresql 如果引用存在则连接表

postgresql 关系不存在

postgresql 如何将图像文件保存在 Postgres 数据库中？

相关推荐

最近更新

标签