Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

plperl RFC: (perl embedded in postgreSQL) An Oracle decode replacement

by Madams (Monk)
on Apr 09, 2006 at 23:13 UTC ( #542185=perlquestion: print w/ replies, xml ) Need Help??
Madams has asked for the wisdom of the Perl Monks concerning the following question:

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.

Comment on plperl RFC: (perl embedded in postgreSQL) An Oracle decode replacement
Select or Download Code
Re: plperl RFC: (perl embedded in postgreSQL) An Oracle decode replacement
by zentara (Archbishop) on Jan 31, 2007 at 11:05 UTC
    That Unitarian Jihad Name stuff is hilarious......, yours truly, The Shotgun of Warm Humanitarianism.

    I'm not really a human, but I play one on earth. Cogito ergo sum a bum
Re: plperl RFC: (perl embedded in postgreSQL) An Oracle decode replacement
by jplindstrom (Monsignor) on Feb 01, 2007 at 14:17 UTC
    coalesce is actually supported by Oracle, and since it is the standard way to do it, that's the one I prefer to use whenever possible.
      Actually "case x when bool then y when bool then z else zz end" is the standard compliant way to do oracle's decode

      However I do like the "speed" and conciseness of the decode vs case. But as stated in the OP, I hacked this up to use for "work emulation". We use an ERP that uses Oracle 8i and the code base is littered with decodes, so to stop being asked to explain why my code doesn't "look like" the code from the ERP company's coders, I've just caved...

      In my own code that doesn't "touch" the database directly (in a spreadsheet "solution" (ick..) or personal utility query) I use case.

      Plus it was gobs of fun to write the plperlu script and find out how they set up the Safe container for plperl....


      "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.

        Ah, yes, I was only talking about the nvl vs coalesce thing :)

        Maybe not that obvious since the rest of the post wasn't about that. Sorry about the confusion.

        /J

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://542185]
Approved by idsfa
Front-paged by planetscape
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (6)
As of 2014-12-18 00:18 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (41 votes), past polls