At my job we use Oracle 8i, and at home I use postgreSQL...
So...I like to sometimes prototype stuff on my home system before useing it at work on Oracle. There are various Oracle
<-> postgreSQL match ups such as:
nvl <-> coalesce
decode <-> case x when bool then y when bool then z else zz end
cube <-> contrib/crosstab (to some extent..)
The "stinker" is the decode <-> case construct conversion. The case construct is (I think) more SQL standard compliant, but far less convenient. Therefore I've tried to make a plperlu version for postgreSQL.
The wisdom I seek is manifold:
- Is this relatively robust? (it hasn't yet croaked on my data, but YMMV)
- Is it relatively efficient?
- Should I use locale; ? And are perl/postgreSQL locale issues now relatively fixed in postgreSQL [I know, that last is kinda OT here but the postgreSQL mailing lists are somewhat vague on the subject :( ]
And lastly...
- Would anyone find this useful?
- Update:Anyone have other useful plperlisms?
UPDATE (2006-04-12):
Given a table that looks like:
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)
You can call it as below (remember || is postgreSQL's string concat op, and $$..$$ is postgreSQL's version of q//):
select pl.decode(col_1,$$<75,,$$||col_2||$$<-value,,<250,,$$||col_1||$
+$,,default value$$) from xl_odbc_test;
Which is equivalent to:
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;
And the result is:
decode
---------------
a<-value
b<-value
c<-value
xx<-value
yy<-value
bb<-value
100
200
default value
(9 rows)
Code below:
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 l
+isting of
## items to match and items to return if the match is
+successful.
## The last entry in the string is the final "else" re
+turn value.
## The match sections may include standard perl boolea
+n
## 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 st
+art 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 macr
+o in postgresql's
# http://developer.postgresql.org/cvsweb.cgi/pgsql/src/pl/plperl/p
+lperl.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 en
+tries: ie -
# ( '>10' , 'return#1' , 'final else' ) or ( '>10' , 'retur
+n#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() an
+d ignore it,
# then move on and try the next set of matc
+h/result clauses...
$error =~ s/ at line.+//;
$error =~ s/trapped.+/deemed unsafe/;
chomp $error;
elog( NOTICE , "pl.decode(): potentially dangerous operation f
+ound, "
. $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 pu
+blic;
"All too often people confuse their being able to think with their actually having done so. A more pernicious mistake does not exist."
--Moraven Tollo in Michael A. Stackpole's A Secret Atlas
My Unitarian Jihad Name is: Sibling Pepper Spray of Loving Kindness. Get yours.
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.