Find first non-null value along a path (array of nodes) in a hierarchical table

arrays, hierarchy, plpgsql, postgresql, postgresql-9.1

Solution

You can largely simplify this whole endeavor with the use of a RECURSIVE CTE, available in PostgreSQL 8.4 or later:

Test table (makes it easier for everyone to provide test data in a form like this):

CREATE TEMP TABLE tbl (
    id int
  , hierarchy int[]
  , abbreviation1 text
  , abbreviation2 text
);

INSERT INTO tbl VALUES
 (1, '{1}',     'SB', 'GL')
,(2, '{2,1}',   NULL, NULL)
,(3, '{3,2,1}', NULL, 'TC')
,(4, '{4,2,1}', NULL, NULL);

Query:

WITH RECURSIVE x AS (
    SELECT id
         , COALESCE(abbreviation1, abbreviation2) AS abbr
         , hierarchy[2] AS parent_id
    FROM   tbl

    UNION ALL
    SELECT x.id
         , COALESCE(parent.abbreviation1, parent.abbreviation2) AS abbr
         , parent.hierarchy[2] AS parent_id
    FROM   x
    JOIN   tbl AS parent ON parent.id = x.parent_id
    WHERE  x.abbr IS NULL  -- stop at non-NULL value
    )
SELECT id, abbr
FROM   x
WHERE  abbr IS NOT NULL  -- discard intermediary NULLs
ORDER  BY id

Returns:

id | abbr
---+-----
1  | SB
2  | SB
3  | TC
4  | SB

This presumes that there is a non-null value on every path, or such rows will be dropped from the result.

Problem

I have been fruitlessly trying for several hours to make a function that filter array subscripts based upon a criteria on the array from which the subscripts and then create an array of those subscripts. The data structure I am dealing with is similar to the following sample (except with many more columns to compare and more complicated rules and mixed data types): ``` id hierarchy abbreviation1 abbreviation2 1 {1} SB GL 2 {2,1} NULL NULL 3 {3,2,1} NULL TC 4 {4,2,1} NULL NULL ``` I need to run a query that takes the next non-null value closest to the parent for abbreviation1 and abbreviation2 and compares them based upon the hierarchical distance from the current record in order to get a single value for an abbreviation. So, for example, if the first non-null values of abbreviation1 and abbreviation2 are both on the same record level abbreviation1 would take priority; on the other hand, if the first non-null abbreviation2 is closer to the current record then the corresponding non-null value for abbreviation1, then abbreviation2 would be used. Thus the described query on the above sample table would yield; ``` id abbreviation 1 SB 2 SB 3 TC 4 SB ``` To accomplish this task I need to generate a filtered array of array subscripts (after doing an `array_agg()` on the abbreviation columns) which only contain subscripts where the value in an abbreviation column is not null. The following function, based on all the logic in my tired mind, should work but does not ``` CREATE OR REPLACE FUNCTION filter_array_subscripts(rawarray anyarray,criteria anynonarray,dimension integer, reverse boolean DEFAULT False) RETURNS integer[] as $$ DECLARE outarray integer[] := ARRAY[]::integer[]; x integer; BEGIN for i in array_lower(rawarray,dimension)..array_upper(rawarray,dimension) LOOP IF NOT criteria IS NULL THEN IF NOT rawarray[i] IS NULL THEN IF NOT rawarray[i] = criteria THEN IF reverse = False THEN outarray := array_append(outarray,i); ELSE outarray := array_prepend(i,outarray); END IF; ELSE IF reverse = False THEN outarray := array_append(outarray,i); ELSE outarray := array_prepend(i,outarray); END IF; END IF; END IF; ELSE IF NOT rawarray[i] is NULL THEN IF reverse = False THEN outarray := array_append(outarray,i); ELSE outarray := array_prepend(i,outarray); END IF; END IF; END IF; END LOOP; RETURN outarray; END; $$ LANGUAGE plpgsql; ``` For example, the below query returns `{5,3,1}` when it should return `{5,4,2,1}` ``` select filter_array_subscripts(array['This',NULL,'is',NULL,'insane!']::text[] ,'is',1,True); ``` I have no idea why this does not work, I have tried using the `foreach` array iteration syntax but I cannot figure out how to cast the iteration value to the scalar type contained within the `anyarray`. What can be done to fix this?

Original source