SQL - Function to return the next value of an array if it doesn't exist

Question

I'm trying to build a function (on PostgreSQL 9.6.11) with 3 parameters :

list of ordered values (array of integers)
value (integer)
substract (integer)

The aim : if (value - substract) is in the array -> return (value - substract). If not, I would like to return the next value available in the array (next position)

For example I have this array [2010, 2009, 2008, 2007, 2006, 1999]

If I have value = 2008 and substract = 2, I expect the return 2006 (2006 is in the array)

If I have value = 2008 and substract = 3, I expect the return 1999 (2005 is not in the array, the next value available is 1999)

For now, I'm here :

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);

Thks for help !

There are some implied assumptions here, and some ambiguities too. 1). Are the values in the array always ordered, and ordered descending? 2). If you have array = [10, 8, 6, 4, 2], value = 9, subtract = 4 I'm assuming the desired return should be 4? 3). If that's correct, isn't it just a case of return the largest value in the array that is less than or equal to value - subtract? (Meaning that the order of the array is irrelevant?) — MatBailie
– MatBailie, Commented Mar 28, 2019 at 10:45
Yep, the values in the array are always ordered descending, if the value - substract is not in the array, I take the max value that is less than or equal to value - substract (so I had a condition in the function of a_horse_with_no_name : "AND t.v != value - substract" to do it) — RemiF
– RemiF, Commented Mar 28, 2019 at 13:20

user330315 · Accepted Answer · 2019-03-28 10:21:22Z

3

You can do this with a SQL function, no need for 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;

The unnest returns all values as rows, the where clause limits that to those smaller than the result of value - substract and then highest number is returned. If that is the same as value - substract that will be returned, otherwise the next highest.

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

returns 1999

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

returns 2006

answered Mar 28, 2019 at 10:21

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL - Function to return the next value of an array if it doesn't exist

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related