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;

Side note: these are case expressions, not case statements. I updated the question tag and title accordingly. — GMB
– GMB, Commented Feb 29, 2020 at 1:08
@GMB Ah thank you, I apologize for that. It was an oversight on my part when tagging the question. — Ben
– Ben, Commented Feb 29, 2020 at 1:35

GMB · Accepted Answer · 2020-02-29 01:05:49Z

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.

answered Feb 29, 2020 at 1:05

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

Ben Over a year ago

Thank you, this is perfect and what I'm looking for. Eventually I'm going to need to scale this so this is where I was stumped. I had been looking at COALESCE and tinkering with it but I could never get the results I wanted.

Gordon Linoff · Accepted Answer · 2020-02-29 00:57:03Z

1

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)

answered Feb 29, 2020 at 0:57

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

Ben Over a year ago

Id rather nest them at that point, I'm not sure creating a larger case expression is what I'm looking for. I do appreciate the insight though!

sticky bit · Accepted Answer · 2020-02-29 01:08:45Z

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;

This isn't far off from what I need but I'm looking for it to be a bit more... readable I suppose. Eventually this is going to be scaled out to a couple teams and I need them to be able to deploy this same function. This looks great though, thank you!

Collectives™ on Stack Overflow

Nested Case Expressions in SQL Functions with Multi column input

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related