使用变化的参数测试功能是否为空 [英] 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?

推荐答案

我不同意其他答案中的某些建议.可以使用plpgsql完成此操作，我认为与在客户端应用程序中组合查询相比，优越得多.它更快，更干净，并且应用程序仅在请求中通过电线发送最低要求. SQL语句保存在数据库内部，这使得维护更加容易-除非要在客户端应用程序中收集所有业务逻辑，否则这取决于常规体系结构.

I disagree with some of the advice in other answers. This can be done with plpgsql 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.

• 您不需要在SELECT周围带有RETURN QUERY

从不使用name和id作为列名.它们不是描述性的，当您加入一堆表时(例如必须在关系数据库中使用a lot)，最终将得到几列均名为name或id的列.啊.

Never use name and id as column names. They are not descriptive and when you join a bunch of tables (like you have to a lot in a relational database), you end up with several columns all named name or id. Duh.

至少在询问公共问题时，请正确格式化SQL.但是，为了自己的利益，也要私下进行.

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

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 $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$ LANGUAGE plpgsql;

致电:

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 positional notation, named notation or mixed notation in the function call. More in this related answer:
    Functions with variable number of input parameters

    有关动态SQL基础的更多说明，请参见以下相关答案:
    重构PL/pgSQL函数返回各种SELECT查询的输出

    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

    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的> 子句使SQL注入成为不可能，并允许直接使用参数值，就像准备好的语句一样.

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

    NULL值在此处用于忽略参数.它们实际上并不用于搜索.

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

    您可以使用简单的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. 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.

    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 ($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$ LANGUAGE sql;
    

    完全相同.

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

    To effectively ignore parameters with NULL values:

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

    如果您实际上想使用 NULL值作为参数，请改用以下结构:

    If you actually want to 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.
    Also replace all instances of LEFT JOIN with JOIN.

    SQL小提琴 ，并提供了适用于所有变体的简化演示.

    SQL Fiddle with simplified demo for all variants.

    这篇关于使用变化的参数测试功能是否为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！