SQL - 如果数组不存在则返回数组的下一个值的函数

Question

我正在尝试使用 3 个参数构建一个函数(在 PostgreSQL 9.6.11 上):

• 有序值列表(整数数组)
• 值(整数)
• 减去(整数)

目标:如果 (value - substract) 在数组中 -> return (value - substract)。如果没有，我想返回数组中的下一个可用值(下一个位置)

例如我有这个数组 [2010, 2009, 2008, 2007, 2006, 1999]

如果我有 value = 2008 和 substract = 2，我期望返回 2006(2006 在数组中)

如果我有 value = 2008 和 substract = 3，我期望返回 1999(2005 不在数组中，下一个可用值是 1999)

现在，我在这里:

CREATE OR REPLACE FUNCTION _fonctions_globales.check_year_exist(liste INT[], value integer, substract integer)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
BEGIN

    IF EXISTS (SELECT 1 WHERE (value - substract) = ANY(liste)) THEN
          return value - substract;
    ELSE 
          return ?;
    END IF;
END; $function$

SELECT _fonctions_globales.check_year_exist(array[2010, 2009, 2008, 2007, 2006, 1999], 2008, 3);

谢谢你的帮助!

Answer 1

您可以使用 SQL 函数来完成此操作，无需 PL/pgSQL:

CREATE OR REPLACE FUNCTION check_year_exist(liste INT[], value integer, substract integer)
 RETURNS integer
AS 
$function$
  select max(v)
  from unnest(liste) as t(v)
  where t.v <= value - substract;
$function$
 LANGUAGE sql;

unnest 将所有值作为行返回，where 子句将其限制为小于 value - substract 结果的那些值，然后返回最大数。如果它与将返回的 value - substract 相同，否则次高。

SELECT check_year_exist(array[2010, 2009, 2008, 2007, 2006, 1999], 2008, 3);

返回 1999

SELECT check_year_exist(array[2010, 2009, 2008, 2007, 2006, 1999], 2008, 2);

返回 2006