Waiting for 8.4 – CASE in pl/PgSQL

Another patch from Pavel Stehule – committed by Tom Lane. This patch adds CASE construction to pl/PgSQL:

Commit log:

Support SQL/PSM-compatible CASE statement in plpgsql.
Pavel Stehule

Of course we had CASE for a long time, but it was SQL CASE. This patch adds CASE as a statement similar to IF.

There are 2 basic forms of CASE that are supported:

CREATE OR REPLACE FUNCTION x(INT4) RETURNS TEXT AS $$
BEGIN
    CASE $1
    WHEN 1,2 THEN RETURN 'one OR two';
    WHEN 3 THEN RETURN 'three';
    ELSE RETURN 'dont know';
    END CASE;
END;
$$ LANGUAGE plpgsql;

The way the function works is pretty obvious:

# SELECT x(1);
     x
------------
 one OR two
(1 ROW)
# SELECT x(2);
     x
------------
 one OR two
(1 ROW)
# SELECT x(3);
   x
-------
 three
(1 ROW)
# SELECT x(4);
     x
-----------
 dont know
(1 ROW)

There is also a way to use it with any boolean expression:

CREATE OR REPLACE FUNCTION x(INT4) RETURNS TEXT AS $$
BEGIN
    CASE
    WHEN $1 < 10 THEN RETURN 'less than 10';
    WHEN $1 = 10 THEN RETURN 'it''s 10';
    ELSE RETURN 'more than 10';
    END CASE;
END;
$$ LANGUAGE plpgsql;

Test:

# SELECT x(5);
      x
--------------
 less than 10
(1 ROW)
# SELECT x(10);
    x
---------
 it's 10
(1 row)
# select x(11);
      x
--------------
 more than 10
(1 row)

The thing I found very cool, is that it properly (at lease for my values of “properly") handles cases where there is no good “when" for given value, and there is no “else" block:

CREATE OR REPLACE FUNCTION x(INT4) RETURNS TEXT AS $$
BEGIN
    CASE
    WHEN $1 < 10 THEN RETURN 'less than 10';
    WHEN $1 = 10 THEN RETURN 'it''s 10';
    END CASE;
END;
$$ LANGUAGE plpgsql;

And now, let's test what it will do when called with value 20:

# SELECT x(20);
ERROR:  CASE NOT found
HINT:  CASE statement IS missing ELSE part.
CONTEXT:  PL/pgSQL FUNCTION "x" line 2 at CASE

Brilliant. Proper exception, which will instantly show you if you forgot about some possible value/condition.

6 thoughts on “Waiting for 8.4 – CASE in pl/PgSQL”

  1. hi,

    but in your example there is no real difference from the

    return CASE $1 WHEN ‘bla’ then ‘BLA’ else ‘BLU’ end;

    Probably it is better to try some better example… probably this construct will make triggers look better 🙂

  2. @Valentine Gogichashvili:
    yes, i could have provide better example. the biggest difference is that within pl/pgsql case i can put pl/pgsql constructs. like variable assignment, loops, and anything else.

    in sql case (which you showed) i cannot – i can simply choose which value to return to external expression (“return” in your case).

  5. Pingback: PostgreSQL 8.4: Le novità nelle stored procedure - 2ndQuadrant | 2ndQuadrant

Comments are closed.