Modify COALESCE function to consider two adjacent values for the result

Question

I'm currently using the COALESCE function to grab the first NOT NULL value from a list of fields. Example:

 COALESCE(header_to_node_13.subsetname, header_to_node_12.subsetname,
 header_to_node_11.subsetname, header_to_node_10.subsetname, 
 header_to_node_9.subsetname, header_to_node_8.subsetname, 
 header_to_node_7.subsetname, header_to_node_6.subsetname,   
 header_to_node_5.subsetname, header_to_node_4.subsetname,
 header_to_node_3.subsetname, header_to_node_2.subsetname,     
 header_to_node_1.subsetname, 
 header_to_node.subsetname, header_to_node.setname) AS prctr1

I need to modify the logic so that I grab the value to the right of the COALESCE value as well, so the two values can be compared to populate a newly created field.

For instance, if the COALESCE value is header_to_node_5.subsetname I need to grab header_to_node_4.subsetname, as well, so the two can be used in a CASE statement.

If the second value (header_to_node_4.subsetname) IS NULL I want the first value (header_to_node_5.subsetname). If the second value is NOT NULL I want to use the second value.

I'm stumped on how to grab the second value. I am using Greenplum/PostgreSQL 8.2

Using [massive CASE](https://data.stackexchange.com/stackoverflow/query/379240). — Lukasz Szozda, Oct 22 '15 at 20:27

Giorgos Betsos · Answer 1 · 2015-10-22T20:49:02.310

If I understand your requirement correctly you have an ordered set of fields, possibly coming from joining together several tables, and you want to obtain the first non-null field and the field immediately following it.

In this case you can use LATERAL: this allows you to reference fields provided by preceding FROM items:

SELECT id, COALESCE(t.b, t.a) AS result
FROM mytable,
LATERAL (
  SELECT x.v, LEAD(x.v) OVER (ORDER BY x.i) 
  FROM (
    VALUES (header_to_node_13, 1), (header_to_node_12, 2),
           (header_to_node_11, 3), (header_to_node_10, 4),
           (header_to_node_9, 5), (header_to_node_8, 6),
           (header_to_node_7, 7), (header_to_node_6, 8),
           (header_to_node_5, 9), (header_to_node_4, 10),
           (header_to_node_3, 11), (header_to_node_2, 12),
           (header_to_node_1, 13)) AS x(v,i)
  ORDER BY 
     CASE WHEN x.v IS NULL THEN 1 ELSE 0 END,
     x.i
  LIMIT 1 ) AS t(a, b)

Using VALUES inside the LATERAL subquery essentially constructs a table containing all fields referenced by the COALESCE function of your query. An order field, x.i, is also included in VALUES so as to define field order.

Once this in-line table is set-up you can easily perform an ORDER BY, together with LIMIT 1, on all fields so as to find the first non-null field. LEAD window function is used to get next field.

Note: For the sake of simplicity I have renamed fields present in the OP, e.g. header_to_node_13.subsetname to header_to_node_13. Also I have omitted some of the fields inside COALESCE.

Demo here

This looks great. Unfortunately the LATERAL feature is unsupported in the version of PostgreSQL that I am using. — user3329160, Oct 27 '15 at 20:08

score 1 · Answer 2 · answered Oct 22 '15 at 20:52

This should work in Postgres 9.4 and up:

SELECT val, nextval
FROM
(
  SELECT
    val,
    lag(val) OVER w as prevval,
    lead(val) OVER w as nextval
  FROM UNNEST(ARRAY[
    header_to_node_13.subsetname, header_to_node_12.subsetname,
    header_to_node_11.subsetname, header_to_node_10.subsetname, 
    header_to_node_9.subsetname, header_to_node_8.subsetname, 
    header_to_node_7.subsetname, header_to_node_6.subsetname,   
    header_to_node_5.subsetname, header_to_node_4.subsetname,
    header_to_node_3.subsetname, header_to_node_2.subsetname,     
    header_to_node_1.subsetname, 
    header_to_node.subsetname, header_to_node.setname
  ]) WITH ORDINALITY AS u(val, pos)
  WINDOW w AS (ORDER BY pos)
) t
WHERE
  prevval IS NULL AND
  val IS NOT NULL

score 1 · Answer 3 · edited Jun 20 '20 at 09:12

I simplified the column names for clarity.

Function

For any input type and any number of values, make it a polymorphic function taking a variable number of parameters (VARIADIC):

CREATE OR REPLACE FUNCTION coalesce2(VARIADIC ANYARRAY)
  RETURNS ANYELEMENT AS
$func$
BEGIN

FOR i IN 1 .. array_upper($1, 1)
LOOP
   IF $1[i] IS NOT NULL THEN
      RETURN COALESCE($1[i+1], $1[i]);
   END IF;
END LOOP;

RETURN NULL;  -- we only come this far if all values are NULL

END
$func$ LANGUAGE plpgsql IMMUTABLE;

Call:

SELECT coalesce2(h13, h12, h11, h10, ...);

VARIADIC parameters are visible as one array inside the function and we can loop with FOREACH:

Iterating over integer[] in PL/pgSQL

Or you could use a plain SQL function to encapsulate the logic of one of the following:

Pure SQL

SELECT COALESCE(lead(elem) OVER (ORDER BY ord), elem) AS result
FROM   unnest(ARRAY[h13, h12, h11, h10, ...]) WITH ORDINALITY u(elem, ord)
ORDER  BY elem IS NULL, ord
LIMIT  1;

WITH ORDINALITY (like @Nick already displayed) requires Postgres 9.4 or later. This solution works as defined by the SQL standard. It's not strictly needed, though. This works, too:

SELECT COALESCE(lead(elem) OVER (), elem) AS result
FROM   unnest(ARRAY[h13, h12, h11, h10, ... ]) elem
ORDER  BY elem IS NULL
LIMIT  1;

It relies on the Postgres implementation of unnest() that preserves the order of array elements, and the window function lead() works with this given order, before the later ORDER BY reorders rows. Not strictly guaranteed by the SQL standard, but it works with all available versions of Postgres. Consider:

PostgreSQL unnest() with element number

You could also use a VALUES expression with manual numbering to be safe and standard-compliant like @Giorgos demonstrated or with my simplified query:

SELECT COALESCE(lead(elem) OVER (), elem) AS result
FROM  (VALUES (h13), (h12), (h11), (h10), ... ) t(elem)
ORDER  BY elem IS NULL
LIMIT  1;

SQL Fiddle demonstrating all.

I am using PostgreSQL 8.2 so I cannot use ORDINALITY. I tried the variadic function but it errors out: LINE 111: COALESCE2(vw_header_to_node_13.subsetname, ^ HINT: No function matches the given name and argument types. You may need to add explicit type casts. — user3329160, Oct 27 '15 at 20:34
@user3329160:Why would you still use the obsolete version 8.2? The fiddle demonstrates the the function works in modern Postgres. — Erwin Brandstetter, Oct 28 '15 at 11:08
Because it Greenplum specific, so EMC has their own version customized and packaged with the system. — user3329160, Oct 28 '15 at 14:22
@user3329160: There is a tag for Greeplum - which is related to, but not exactly Postgres. Please always tag your questions accordingly. — Erwin Brandstetter, Oct 28 '15 at 15:51
Is there a way that this can be performed with the system that I'm working with? — user3329160, Oct 29 '15 at 14:40

Modify COALESCE function to consider two adjacent values for the result

3 Answers3

Function

Pure SQL