用不同的参数测试函数中的空值 [英] Test for null in function with varying parameters

问题描述

我有一个 Postgres 函数:

I have a Postgres function:

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 = $7 
                and p.name = $6
                    and s.name = $5
                    and a.ad_nr = $1 
                    and a.ad_nr_extra = $2
                    and a.ad_info = $3
                    and ad_postcode = $4);
END;
$$
LANGUAGE plpgsql;

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

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

如何测试查询中的 NULL?

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

推荐答案

我不同意其他答案中的一些建议.这可以通过 PL/pgSQL 完成，我认为它在很大程度上远优于在客户端应用程序中组装查询.它更快更干净，应用程序仅在请求中通过网络发送最低限度.SQL 语句保存在数据库内部，这样维护起来更容易——除非你想收集客户端应用程序中的所有业务逻辑，这取决于通用架构.

I disagree with some of the advice in other answers. This can be done with PL/pgSQL and I think it is mostly far superior to 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.

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)
  LANGUAGE plpgsql 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 $1 IS NOT NULL THEN 'a.ad_nr = $1' END
      , CASE WHEN $2 IS NOT NULL THEN 'a.ad_nr_extra = $2' END
      , CASE WHEN $3 IS NOT NULL THEN 'a.ad_info = $3' END
      , CASE WHEN $4 IS NOT NULL THEN 'a.ad_postcode = $4' END
      , CASE WHEN $5 IS NOT NULL THEN 's.name = $5' END
      , CASE WHEN $6 IS NOT NULL THEN 'p.name = $6' END
      , CASE WHEN $7 IS NOT NULL THEN 'c.name = $7' END
   )
   )
   USING $1, $2, $3, $4, $5, $6, $7;
END
$func$;

调用:

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

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

由于所有函数参数都有默认值，您可以使用位置表示法、命名表示法或混合 符号.见:

Since all function parameters have default values, you can use positional notation, named notation or mixed notation at your choosing in the function call. See:

• 输入参数数量可变的函数

关于动态 SQL 基础的更多解释:

More explanation for basics of dynamic SQL:

• 重构 PL/pgSQL 函数返回各种 SELECT 查询的输出

concat() 函数有助于构建字符串.它是在 Postgres 9.1 中引入的.

The concat() function is instrumental for building the string. It was introduced with Postgres 9.1.

CASE 语句的 ELSE 分支在不存在时默认为 NULL.简化代码.

The ELSE branch of a CASE statement defaults to NULL when not present. Simplifies the code.

使用EXECUTE 的 code> 子句使 SQL 注入成为不可能，因为值作为 values 传递并允许直接使用参数值，就像在准备好的语句中一样.

The USING clause for EXECUTE makes SQL injection impossible as values are passed as values and allows to use parameter values directly, exactly like in prepared statements.

NULL 值在这里用来忽略参数.它们实际上并不用于搜索.

NULL values are used to ignore parameters here. They are not actually used to search.

RETURN QUERY 的 SELECT 不需要括号.

您可以用一个普通的 SQL 函数来完成它并避免动态 SQL.对于某些情况，这可能更快.但我不希望在这种情况下.在没有不必要的连接和谓词的情况下规划查询通常会产生最佳结果.像这样的简单查询的规划成本几乎可以忽略不计.

You could do 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. Planning the query without unnecessary joins and predicates typically produces best results. Planning cost for a simple query like this is almost negligible.

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)
  LANGUAGE sql 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 ($1 IS NULL OR a.ad_nr = $1)
AND   ($2 IS NULL OR a.ad_nr_extra = $2)
AND   ($3 IS NULL OR a.ad_info = $3)
AND   ($4 IS NULL OR a.ad_postcode = $4)
AND   ($5 IS NULL OR s.name = $5)
AND   ($6 IS NULL OR p.name = $6)
AND   ($7 IS NULL OR c.name = $7)
$func$;

相同的调用.

要有效地忽略带有NULL值的参数:

To effectively ignore parameters with NULL values:

($1 IS NULL OR a.ad_nr = $1)

要实际使用NULL 值作为参数，请改用以下结构:

To actually use NULL values as parameters, use this construct instead:

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

这也允许使用索引.
对于手头的情况，将 LEFT JOIN 的所有实例替换为 JOIN.

This also allows for indexes to be used.
For the case at hand, replace all instances of LEFT JOIN with JOIN.

db<>fiddle 这里 -带有适用于所有变体的简单演示.
_旧sqlfiddle

• 不要使用 name 和 id 作为列名.它们不是描述性的，当你加入一堆表时(就像你在关系数据库中a lot所做的那样)，你最终会得到几列都命名为 name 或 id，并且必须附加别名以对混乱进行排序.

• Don't use name and id as column names. They are not descriptive and when you join a bunch of tables (like you do to a lot in a relational database), you end up with several columns all named name or id, and have to attach aliases to sort the mess.

请正确格式化您的 SQL，至少在询问公众问题时是这样.但为了你自己的利益，也请私下这样做.

Please format your SQL properly, at least when asking public questions. But do it privately as well, for your own good.

这篇关于用不同的参数测试函数中的空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！