nvl <-> coalesce
decode <-> case x when bool then y when bool then z else zz end
cube <-> contrib/crosstab (to some extent..)

##</code><code>##

playtest=# select * from xl_odbc_test;
 col_1 |     col_2
-------+---------------
     1 | a
     2 | b
     3 | c
    10 | xx
    20 | yy
    30 | bb
   100 | hundred
   200 | two hundred
   300 | three hundred
(9 rows)

##</code><code>##

select pl.decode(col_1,$$<75,,$$||col_2||$$<-value,,<250,,$$||col_1||$$,,default value$$) from xl_odbc_test;

##</code><code>##

select case when col_1 < 75  then col_2 || '<-value'
            when col_1 < 250 then cast(col_1 as text)
            else 'default value'
       end
from xl_odbc_test;

##</code><code>##

    decode
---------------
 a<-value
 b<-value
 c<-value
 xx<-value
 yy<-value
 bb<-value
 100
 200
 default value
(9 rows)

##</code><code>##

CREATE OR REPLACE FUNCTION pl.decode(selector text, clauses text)
  RETURNS text AS
$BODY$

################################################################################
##      decode( selector text , clauses text )
##
##              plperlu rendition of Oracle's decode() function.
##
##      Takes 2 args:
##
##              1. (selector).  The item to check, and
##              2. (clauses) .  A double comma (,,) separated string listing of
##                 items to match and items to return if the match is successful.
##                 The last entry in the string is the final "else" return value.
##                 The match sections may include standard perl boolean
##                 operations.
##
##      USE DOLLAR QUOTING to setup the test/result string,
##      it WILL save you much hair pulling.
##
##      If you want a return item to be NULL for an option, use one of the
##      following (case INSENSITVE) return values:
##              null()   or
##              undef()
##
##      decode() uses its own Safe.pm compartment for the reevalution of the
##      match clauses.
##
################################################################################


use Safe;

my $vault;

# get / setup a safe "vault"  from / in %_SHARED to reduce function start up time on a per session level

if ( exists( $_SHARED{pl_vault} ) && defined( $_SHARED{pl_vault} ) ){

	$vault = $_SHARED{pl_vault};
	#elog( NOTICE , "plperlu: Preloaded safety vault being used." );
}
else {
	# setup a safe vault using the same parameters as the SAFE_OK macro in postgresql's
	# http://developer.postgresql.org/cvsweb.cgi/pgsql/src/pl/plperl/plperl.c?rev=1.105

	$vault = Safe->new;
	$vault->permit_only( qw/ :default :base_math !:base_io time sort / );

	$_SHARED{pl_vault} = $vault;
	#elog( NOTICE , "plperlu: Setting up session safety vault.");
}

my $selector   = $_[0];
my @in_clauses = split( /,,/ , $_[1] );

#reject @in_clauses argument if it doesn't contain an odd number of entries: ie - 
#            ( '>10' , 'return#1' , 'final else' ) or ( '>10' , 'return#1' , '<10' , 'return#2' , 'final else' )  is OK 
#            ( '>10' , 'return#1' ) is not

die   "pl.decode(): invalid clause argument, the number of entries was not odd.\n"
    unless ( scalar( @in_clauses ) % 2 );

my $final_else = pop @in_clauses;
my $retval     = undef;
my $have_match = undef;

ITERATIONS:
while ( @in_clauses ){
	my $match_clause = shift @in_clauses;
	my $then_clause  = shift @in_clauses;
	my $result       = $vault->reval( $selector . $match_clause );

	if ( my $error = $@ ){
		# safe reval error...clean up the error message then elog() and ignore it,
        #                    then move on and try the next set of match/result clauses...
		
        $error =~ s/ at line.+//;
		$error =~ s/trapped.+/deemed unsafe/;
		chomp $error;
		elog( NOTICE , "pl.decode(): potentially dangerous operation found, "
                             . $error
                             . ", skipping clause..."
                             );
		next ITERATIONS;
	}
	if ( $result ){ # we have the winner...set $retval and bail out...we only grab the first true result...

		$retval     = $then_clause;
		$have_match = "yes";
		last ITERATIONS;
	}
	# no $result? oh well try the next set...
}

if ( defined( $have_match ) ){
        # last check to see if we've matched anything...
        # and if so return it...accounting for the case where
        # the wanted return is NULL... 
	if ( $retval =~ m/^ (?: null || undef ) \( \) $ /ix ) {
		$retval=undef;
	}
	return $retval;
}

# if we get here we are returning the "default" result value
# also accounting for the case where the wanted return is NULL...
if ( $final_else =~ m/^ (?: null || undef ) \( \) $ /ix ) {
       $final_else=undef;
}

return $final_else;

$BODY$
  LANGUAGE 'plperlu' IMMUTABLE SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION pl.decode(selector text, clauses text) TO public;