May 16th, 2008 by depesz | Tags: , , , | 5 comments »
Did it help? If yes - maybe you can help me? Donate BTC to 19zPa5diT2LZqGtTi8f8bfApLn8rw9zBHx

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.

  1. 5 comments

  2. 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 :)

  3. Jul 13, 2008

    @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).

  4. You are absolutely right, this construct is definitely good to have in a language. Makes it much “sugary” :)

  5. May 10, 2009

    I’m honestly surprised that it’s not already in.

  1. 1 Trackback(s)

  2. Apr 19, 2012: PostgreSQL 8.4: Le novità nelle stored procedure - 2ndQuadrant | 2ndQuadrant

Leave a comment