Nested Case Expressions in SQL Functions with Multi column input

Question

I'm trying to understand if I'm properly nesting these case expressions correctly. Is this the optimal way to go about nesting these?

To explain what I'm trying to do here is create a function that looks at three columns in a single table. A string result is determined based on the found input. If the input is not found in column 1 (aka test), then it moves onto column 2 (transpose) and then to column 3 (construction) until either a result is found or it moves on.

I'm unsure this is the best and or correct way to go about this situation. Any suggestions would be greatly appreciated.

CREATE OR REPLACE FUNCTION any_class(test TEXT, transpose TEXT, construction TEXT) RETURNS TEXT AS $$
    SELECT CASE
        WHEN test IN ('a', 'b') THEN 'result1'
        WHEN test IN ('c', 'd') THEN 'result2'
        WHEN test IN ('e', 'f') THEN 'result3'
        WHEN test IN ('g', 'h') THEN 'result4'
        WHEN test IN ('i', 'j') THEN 'result5'
        WHEN test IN ('k', 'l', 'm') THEN 'result6'
        WHEN test IN ('n') THEN 'result7'
        WHEN test IN ('o') THEN 'result8'
        WHEN test IN ('p') THEN 'result9'
        WHEN test IN ('q', 'r', 's', 't', 'u') THEN 'result10'
        ELSE CASE
            WHEN transpose IN ('a', 'b') THEN 'result1'
            WHEN transpose IN ('c', 'd') THEN 'result2'
            WHEN transpose IN ('e', 'f') THEN 'result3'
            WHEN transpose IN ('g', 'h') THEN 'result4'
            WHEN transpose IN ('i', 'j') THEN 'result5'
            WHEN transpose IN ('k', 'l', 'm') THEN 'result6'
            WHEN transpose IN ('n') THEN 'result7'
            WHEN transpose IN ('o') THEN 'result8'
            WHEN transpose IN ('p') THEN 'result9'
            WHEN transpose IN ('q', 'r', 's', 't', 'u') THEN 'result10'
            ELSE CASE
                WHEN construction IN ('a', 'b') THEN 'result1'
                WHEN construction IN ('c', 'd') THEN 'result2'
                WHEN construction IN ('e', 'f') THEN 'result3'
                WHEN construction IN ('g', 'h') THEN 'result4'
                WHEN construction IN ('i', 'j') THEN 'result5'
                WHEN construction IN ('k', 'l', 'm') THEN 'result6'
                WHEN construction IN ('n') THEN 'result7'
                WHEN construction IN ('o') THEN 'result8'
                WHEN construction IN ('p') THEN 'result9'
                WHEN construction IN ('q', 'r', 's', 't', 'u') THEN 'result10'
                END
            END
    END;
$$
LANGUAGE SQL
IMMUTABLE PARALLEL SAFE;

Answer 1

Maybe the array overlap operator && which checks if two array have at least on element in common could make things a little easier here. Using it you don't need to nest the CASE expressions but have only one.

SELECT CASE
         WHEN ARRAY[test,
                    transpose,
                    construction]
              && ARRAY['a',
                       'b']) THEN
           'result1'
         WHEN ARRAY[test,
                    transpose,
                    construction]
              && ARRAY['c',
                       'd']) THEN
           'result2'
         ...
         WHEN ARRAY[test,
                    transpose,
                    construction]
              && ARRAY['q',
                       'r',
                       's',
                       't',
                       'u']) THEN
           'result10'
       END;

Another option would be to INTERSECT relations using VALUES clauses and check if the intersection is empty with EXISTS.

SELECT CASE
         WHEN EXISTS (SELECT *
                             FROM (VALUES (test),
                                          (transpose),
                                          (construction)) r1 (c1)
                      INTERSECT
                      SELECT *
                             FROM (VALUES ('a'),
                                          ('b')) r2 (c1))
           'result1'
         WHEN EXISTS (SELECT *
                             FROM (VALUES (test),
                                          (transpose),
                                          (construction)) r1 (c1)
                      INTERSECT
                      SELECT *
                             FROM (VALUES ('c'),
                                          ('d')) r2 (c1))
           'result2'
         ...
         WHEN EXISTS (SELECT *
                             FROM (VALUES (test),
                                          (transpose),
                                          (construction)) r1 (c1)
                      INTERSECT
                      SELECT *
                             FROM (VALUES ('q'),
                                          ('r'),
                                          ('s'),
                                          ('t'),
                                          ('u')) r2 (c1))
           'result10'
       END;

Answer 2

The logic in your function looks fine, and I think that it does what you want.

One way to shorten the code (and make it more scalable) would be to use an inner function that checks a single parameter, and then call it multiple times within COALESCE() in the outer function, like so:

CREATE OR REPLACE FUNCTION any_class(test TEXT, transpose TEXT, construction TEXT) 
RETURNS TEXT 
AS $any_class$      
    DECLARE res text;
    BEGIN

    CREATE OR REPLACE FUNCTION one_class(val TEXT) 
    RETURNS text 
    AS $one_class$
        SELECT CASE
            WHEN val IN ('a', 'b') THEN 'result1'
            WHEN val IN ('c', 'd') THEN 'result2'
            WHEN val IN ('e', 'f') THEN 'result3'
            WHEN val IN ('g', 'h') THEN 'result4'
            WHEN val IN ('i', 'j') THEN 'result5'
            WHEN val IN ('k', 'l', 'm') THEN 'result6'
            WHEN val IN ('n') THEN 'result7'
            WHEN val IN ('o') THEN 'result8'
            WHEN val IN ('p') THEN 'result9'
            WHEN val IN ('q', 'r', 's', 't', 'u') THEN 'result10'
      END;
    $one_class$ language sql;

    SELECT COALESCE(one_class(test), one_class(transpose), one_class(construction)) INTO res;
    RETURN res;

    END;
$any_class$ 
language plpgsql;

Demo on DB Fiddle - you can play around with the arguments of the functions to ensure it produces identical results as your existing code.

Answer 3

You can do this with one larger case expression:

(CASE WHEN test IN ('a', 'b') THEN 'result1'
      WHEN test IN ('c', 'd') THEN 'result2'
      . . .
      WHEN transpose IN ('a', 'b') THEN 'result1'
      WHEN transpose IN ('c', 'd') THEN 'result2'
      . . .
      WHEN construction IN ('a', 'b') THEN 'result1'
      WHEN construction IN ('c', 'd') THEN 'result2'
      . . .
 END)

There is no need to nest them.

You seem to want to prioritize the order of looking at the columns. Otherwise, if you wanted to prioritize by the returned values, I would suggest:

(CASE WHEN test IN ('a', 'b') OR transpose IN ('a', 'b') OR construction IN ('a', 'b')
      THEN 'result1'
      . . .
 END)