IIF in postgres

Question

I am attempting to convert an MS-Access query to a postgres statement so I can use it in SSRS. Seems to work great except for the IIF statement.

SELECT labor_sort_1.ncm_id
,IIf(labor_sort_1.sortby_employeeid = 3721
 , ((labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime) * 24 * 29 * labor_sort_1.number_of_ops)
 , IIf(labor_sort_1.sortby_employeeid = 3722
  , ((labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime) * 24 * 24 * labor_sort_1.number_of_ops)
  , IIf(labor_sort_1.sortby_employeeid = 3755, ((labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime) * 24 * 24 * labor_sort_1.number_of_ops)
  , ((labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime) * 24 * 17 * labor_sort_1.number_of_ops)))) AS labor_cost
FROM ...

it returns the following message function iif(boolean, interval, interval) does not exist How would I solve this problem?

Answer 1

You'll need to switch the logic over to a CASE expression. CASE expression are standard for most RDBMS's so it's worth learning. In your case (pun intended) it would translate to:

CASE 
    WHEN labor_sort_1.sortby_employeeid = 3721
        THEN (labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime) * 24 * 29 * labor_sort_1.number_of_ops
    WHEN labor_sort_1.sortby_employeeid = 3722
        THEN (labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime) * 24 * 24 * labor_sort_1.number_of_ops
    WHEN labor_sort_1.sortby_employeeid = 3755
        THEN (labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime) * 24 * 24 * labor_sort_1.number_of_ops
    ELSE
        (labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime) * 24 * 17 * labor_sort_1.number_of_ops)
    END AS labor_cost

Which is a lot cleaner looking since you don't have to monkey with nested iif() issues and all that and should you need to add more employeeids to the list of hard-coded labor costs, it's no biggie.

You might also find it advantageous to us the IN condition instead so you only need two WHEN clauses:

CASE 
    WHEN labor_sort_1.sortby_employeeid = 3721
        THEN (labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime) * 24 * 29 * labor_sort_1.number_of_ops
    WHEN labor_sort_1.sortby_employeeid IN (3722, 3755)
        THEN (labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime) * 24 * 24 * labor_sort_1.number_of_ops
    ELSE
        (labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime) * 24 * 17 * labor_sort_1.number_of_ops)
    END AS labor_cost

Also, you could move the CASE expression into the equation so the logic only needs to determine whatever number you wish to multiply by:

(labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime) 
    * 24    
    * CASE  
        WHEN labor_sort_1.sortby_employeeid = 3721 THEN 29
        WHEN labor_sort_1.sortby_employeeid IN (3722,3755) THEN 24
        ELSE 17
        END 
    * labor_sort_1.number_of_ops AS labor_cost

Answer 2

    ((this is a Wiki, you can edit!))

Same as @Daniel's answer, but generalizing to any datatype.

CREATE or replace FUNCTION iIF(
    condition boolean,       -- IF condition
    true_result anyelement,  -- THEN
    false_result anyelement  -- ELSE
) RETURNS anyelement AS $f$
  SELECT CASE WHEN condition THEN true_result ELSE false_result END
$f$  LANGUAGE SQL IMMUTABLE;

SELECT iif(0=1,1,2);
SELECT iif(0=0,'Hello'::text,'Bye');  -- need to say that string is text.

Good when you are looking for a public-snippets-library.

---

NOTE about IMMUTABLE and "PLpgSQL vs SQL".

• The IMMUTABLE clause is very important for code snippets like this, because, as said in the Guide: "allows the optimizer to pre-evaluate the function when a query calls it with constant arguments"

• PLpgSQL is the preferred language, except for "pure SQL". For JIT optimizations (and sometimes for parallelism) SQL can obtain better optimizations. Is something like copy/paste small piece of code instead of use a function call.

Important conclusion: this function, after optimizations, is so fast than the @JNevill's answer; it will compile to (exactly) the same internal representation. So, although it is not standard for PostgreSQL, it can be standard for your projects, by a centralized and reusable "library of snippets", like pg_pubLib.

Answer 3

I know this has been sitting around for a while but another option is to create a user defined function. If you happen to stumble upon this in your internet searches, this may be a solution for you.

    CREATE FUNCTION IIF(
        condition boolean, true_result TEXT, false_result TEXT
    ) RETURNS TEXT LANGUAGE plpgsql AS $$
    BEGIN
     IF condition THEN
        RETURN true_result;
     ELSE
        RETURN false_result;
     END IF;
    END
    $$;

    SELECT IIF(2=1,'dan the man','false foobar');

Should text not tickle your fancy then try function overloading