How to use coalesce, or a variation, to pull more than 1 value?

Question

This is a question about COALESCE in PostgreSQL. In a current view I am using COALESCE to grab the first NOT NULL value in a list:

COALESCE(vw_header_to_node_13.subsetname,
vw_header_to_node_12.subsetname, 
vw_header_to_node_11.subsetname,
vw_header_to_node_10.subsetname,
vw_header_to_node_9.subsetname,
vw_header_to_node_8.subsetname,
vw_header_to_node_7.subsetname,
vw_header_to_node_6.subsetname,
vw_header_to_node_5.subsetname,
vw_header_to_node_4.subsetname,
vw_header_to_node_3.subsetname,
vw_header_to_node_2.subsetname,
vw_header_to_node_1.subsetname,
vw_header_to_node.subsetname,
vw_header_to_node.setname)
AS prctr1

I have just been informed that instead of grabbing just the first NOT NULL field, I now need to grab the first NOT NULL field and the following 2 fields after the first NOT NULL field. For instance, if vw_header_to_node_8.subsetname was the first NOT NULL field I would want to grab vw_header_to_node_8.subsetname, vw_header_to_node_7.subsetname, & vw_header_to_node_6.subsetname. I know this is not how COALESCE normally functions, but does anyone know of any variations or ways to make this happen?

Answer 1

If all nulls should be excluded from the list, this method should work:

1. convert the list to array,
2. remove nulls from the array,
3. select three first elements of the array.

Example:

with test as (
    select 
        null::text     as v1,
        'apple'::text  as v2,
        null::text     as v3,
        'banana'::text as v4,
        'pear'::text   as v5)

select a[1] val1, a[2] val2, a[3] val3
from (      
    select 
        array_remove(
            array[v1, v2, v3, v4, v5], null) a
    from test
    ) alias

 val1  |  val2  | val3
-------+--------+------
 apple | banana | pear

If the second and third values can be null, we should trim first nulls from the array in step 2. There is no appropriate function in Postgres, but you can write it yourself.

create function array_ltrim_nulls(arr anyarray)
returns anyarray language plpgsql immutable
as $$
declare 
    i integer;
    l integer = array_length(arr, 1);
begin
    for i in 1..l loop
        if arr[i] is not null then
            return arr[i:l];
        end if;
    end loop;
    return null;
end $$;

with test as (
    select 
        null::text     as v1,
        'apple'::text  as v2,
        null::text     as v3,
        'banana'::text as v4,
        'pear'::text   as v5)

select a[1] val1, a[2] val2, a[3] val3
from (      
    select 
        array_ltrim_nulls(
            array[v1, v2, v3, v4, v5]) a
    from test
    ) alias

 val1  | val2 |  val3
-------+------+--------
 apple |      | banana      

---

For easier use I have added the following function to my standard set of functions. Perhaps this function will be most useful to you. The number of function arguments is variable. You only need to ensure that all arguments are of the same type.

create function array_coalesce (variadic arr anyarray)
returns anyarray language sql immutable
as $$
    select array_remove(arr, null);
$$;

select array_coalesce(null::text, 'apple', null, 'banana', 'pear') arr;
         arr         
---------------------
 {apple,banana,pear}

select array_coalesce(null::int, 1, null, 2, 3, 4, null, 5) arr;
     arr     
-------------
 {1,2,3,4,5}

select (array_coalesce(null::int, 1, null, 2, 3, 4, null, 5))[1:3] arr;
     arr     
-------------
 {1,2,3}