Decode equivalent in postgres

Question

There is no equivalent to the Oracle's DECODE()'Function InPostgres`. Is there anyone who wrote decode as a Function?

Answer 1

There is an equivalent. It's called a CASE statement.

There are two forms of CASE:

Simple CASE:

CASE search-expression
    WHEN expression [, expression [ ... ]] THEN
      statements
  [ WHEN expression [, expression [ ... ]] THEN
      statements
    ... ]
  [ ELSE
      statements ]
END CASE;

Searched CASE:

CASE
    WHEN boolean-expression THEN
      statements
  [ WHEN boolean-expression THEN
      statements
    ... ]
  [ ELSE
      statements ]
END CASE;

CASE statements are easier to read; I prefer these over decode() in Oracle.

Answer 2

If you are used to Oracle specific functions, you might want to install PostgreSQL extension orafce.

Among other Oracle specific functions, orafce also implements DECODE - one that you are looking for.

If you are running on Ubuntu, you will simply need to install package postgresql-9.1-orafce to make orafce available in your PostgreSQL server.