Converting a relatively simple call to Oracle's decode() to PostgreSQL...

bleep_bleep_bleeping_oracle.sql

/*
I recently took on a project at work to convert a very large Oracle database to PostgreSQL as part of our transition to AWS. 
Now, I hate Oracle. When I say hate, I'm saying hate because it's unprofessional for me to use any stronger terminology.
Oracle likes to make things fairly easy (all things considered). They put a LOT of effort into creating a lot of little
features that, for the most part, are nice! But... I firmly believe that they do this because they don't want you to leave. 
They WANT you to design yourself into a corner to the point where it's too much work for you to leave. 

Below is a perfect example...

The oracle decode function basically makes for a one line case statement.
Example: decode(value, 0, 'zero', 1, 'one', 'unknown')
   When 'value' equals 0, 'zero' is returned. 
   When 'value' equals 1, 'one' is returned. 
   Else, 'unknown' is returned.

In PostgreSQL, this would look like:
CASE 
   WHEN value=0 THEN 'zero'
   WHEN value=1 THEN 'one'
   ELSE 'unknown'
END

Not difficult... But let's use a real world example.

Below is one of the examples I ran across.                                                                            */

decode(sign(trunc(coalesce(storageRecord.creator_expires, CURRENT_TIMESTAMP + interval '1 days')) - 
   date_trunc('day', CURRENT_TIMESTAMP)), 1, 0, 0, 1, -1, 1, 0)

-- This has now been PostgreSQL-ized like so...

CASE 
  WHEN sign(cast(extract(epoch 
    FROM date_trunc('day', coalesce(storageRecord.creator_expires, CURRENT_TIMESTAMP + interval '1 days')) AS integer)) - 
      sign(cast(extract(epoch FROM date_trunc('day', CURRENT_TIMESTAMP))) AS integer)=1 THEN 0
  WHEN sign(cast(extract(epoch 
    FROM date_trunc('day', coalesce(storageRecord.creator_expires, CURRENT_TIMESTAMP + interval '1 days')) AS integer)) - 
      sign(cast(extract(epoch FROM date_trunc('day', CURRENT_TIMESTAMP))) AS integer)=0 THEN 1
  WHEN sign(cast(extract(epoch 
    FROM date_trunc('day', coalesce(storageRecord.creator_expires, CURRENT_TIMESTAMP + interval '1 days')) AS integer)) - 
      sign(cast(extract(epoch FROM date_trunc('day', CURRENT_TIMESTAMP))) AS integer)=-1 THEN 1
  ELSE 0 
END 

/*
Need I say more? Thanks Oracle for allowing developers of old to design themselves into a corner. Let this be a lesson to
other developers out there... Please, for the love of all that is holy... When possible, please please please, 
do not use platform specific language.
*/

Oracle decode to PostgreSQL example.