Madams has asked for the wisdom of the Perl Monks concerning the following question:
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.
|
---|
Replies are listed 'Best First'. | |
---|---|
Re: plperl RFC: (perl embedded in postgreSQL) An Oracle decode replacement
by zentara (Archbishop) on Jan 31, 2007 at 11:05 UTC | |
Re: plperl RFC: (perl embedded in postgreSQL) An Oracle decode replacement
by jplindstrom (Monsignor) on Feb 01, 2007 at 14:17 UTC | |
by Madams (Pilgrim) on Feb 06, 2007 at 02:44 UTC | |
by jplindstrom (Monsignor) on Feb 06, 2007 at 13:27 UTC |