Simulating iif function

Compatibility Snippets

Normalize space

Works with PostgreSQL

Any version

Written in

SQL

Depends on

Nothing

by Emanuel Calvo Franco and Hector de los Santos

Some developers are accustomed to some 'Access' (and others db's) functions. One of them is the 'iif' function that works very simple and sometimes add to the query more intelligence.

Well, you will surprising to know that in Postgresql is possible and is quite simple to add this function.

In plain SQL:

CREATE OR REPLACE FUNCTION iif_sql(boolean, anyelement, anyelement) returns anyelement as
$body$ select case $1 when true then $2 else $3 end $body$
LANGUAGE sql IMMUTABLE;

while you can cover all datatypes at once with this polymorphic function, the type needs to be defined or specified using casts. A simple iif_sql(8<9,'yes','no') will fail. To solve this, you could add an overloaded method to cover this specific case:

CREATE OR REPLACE FUNCTION iif_sql(boolean, unknown, unknown) returns text as
$body$ select case $1 when true then textin(unknownout($2)) else textin(unknownout($3)) end $body$
LANGUAGE sql IMMUTABLE;

In PL/Pgsql:

CREATE OR REPLACE FUNCTION iif_(boolean, double precision, double precision) RETURNS double precision AS
$body$
DECLARE
	rtVal double precision;
BEGIN
	rtVal := (select case $1 when true then $2 else $3 end);
	return rtVal;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;

In PL/Perl (WARNING - we are doing a diferent way of the same problem, if you want to do it exactly like plpglsql, you must use spi_query_exec() function):

CREATE OR REPLACE FUNCTION iif_perl(boolean, double precision, double precision) RETURNS double precision AS
$body$
if($_[0] =~ /t/){
	return $_[1];
}else{
	return $_[2];
}
$body$
LANGUAGE 'plperl' IMMUTABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER;

Like an C shared object:

#include <stdio.h>
#include "postgres.h"
#include "executor/executor.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PG_FUNCTION_INFO_V1(iif_c);

Datum
iif_c (PG_FUNCTION_ARGS){
   bool message = PG_GETARG_BOOL(0);
   float8 first = PG_GETARG_FLOAT8(1);
   float8 second = PG_GETARG_FLOAT8(2);

   if(message == 1){
      PG_RETURN_FLOAT8(first);
   }else{
      PG_RETURN_FLOAT8(second);
   }
}

But in this way you must execute the compilation and statement 'CREATE FUNCTION':

postgres@pgsql:/usr/local/pgsql84/$ gcc -I include/server/ --shared iif_c.c

...and then:

CREATE OR REPLACE FUNCTION iif_c(boolean, double precision, double precision) RETURNS double precision
     AS '/usr/local/pgsql84/compiled/a.out', 'iif_c'
     LANGUAGE C STRICT;

-- test it? go!
SELECT iif_c(9<8,0,9);

Notice that you must modify the type of parameters to accept another type of values!! (except the first).

Between these functions, we have different 'total runtimes':

plpgsql: Total runtime: 0.091 ms
plperl: Total runtime: 0.116 ms
C: Total runtime: 0.034 ms

What a performance! :D