PLPerl

I l @ ve RuBoard

PL/Perl

Perl is one of the most common scripting languages in use. It runs on almost all platforms and has wide support in the development community. For these reasons, PL/Perl can be an effective choice when choosing a PostgreSQL PL language.

Like PL/Tcl, the PostgreSQL implementation of PL/Perl only enables specific commands, which are deemed trusted. Essentially, any Perl commands that explicitly deal with the file system, environmental settings, or external modules have been disabled.

It is still possible, however, that errant code created in PL/Perl can negatively impact the base system. Most of these problems are because PL/Perl will still allow the exhaustion of memory and endless loops to be created. Therefore, code created in PL/Perl should be closely inspected to ensure that runaway code could not create a problem for the parent system.

General Perl Language Primer

Much of the syntax in PL/Perl is the same as Perl in general. The following is a brief synopsis of how to use Perl. Obviously, if you are new to Perl, consult one of the many books or web sites available for the new Perl user .

Comments

Like many scripting languages, the default comment indicator is the pound sign (#). Any line that begins with the pound sign is ignored entirely.

Control Structures

Perl contains most of the common control structures that are present in other languages. The standard IF structure is as follows :

 if (  expression  ) {      code-statement  } 

Perl also supports more complex IF statements, like IF ELSE and ELSEIF statements.

For instance:

 if (  expression  ) {      code-statement  } elseif (  another-expression  ) {      other-code-statement;  } elseif (  another-expression  ) {      other-code-statement;  } else (  final-expression  ) {      final-code;  } 

Notice in the preceding code how ELSEIF and ELSE statements can be combined to create a chain of test cases and a final default statement to execute if none of the cases test true.

Perl also supports WHILE , UNTIL , DO , FOR , and FOREACH loops; examples are notated in the following:

 while ($a<10) {      print $a;       $a++;  }  until ($a>10) {      print $a;       $a++;  }  do {      print $a;       $a++;  } while ($a<10)  for ($a=0; $a<10; $a++) {      print "Printing 10 times";  }  @lst = ("Jan", "Feb", "Mar");  foreach $a (@lst) {      print $a;  } 

Perl also contains ways to break out-of-control structures like LAST , NEXT , and REDO , and by using labeled blocks. The following is a list of examples:

 while ($a<10) {      print $a;       if ($a=5){           #a is 5, so exit loop            last;       }       $a++;  }  print "exited loop"; 

The preceding code will continue looping until one of two conditions are met: Either $a is greater or equal to 10 or $a is equal to 5. (Actually, in this example, the code will never reach 10 because it will always exit at 5.)

The other statements work similarly. The NEXT statement will reiterate the loop and skip any remaining items; the REDO statement will run the loop again from the beginning without reevaluating the test condition. For example:

 while ($a<10) {      $a++;       print $a;       if ($a=5){           #a is 5, so loop again            next;       }  }  print "exited loop"; 

In addition to just reiterating the loop, label declaratives can be specified in conjunction with the NEXT , LAST , and REDO statements to control program flow:

 OUTER: while ($a<10) {      $a++;       print $a;       INNER: if ($a=5){           REALLYINNER: if ($b=1){           last OUTER;       }       }  }  print "exited loop"; 
Associative Arrays

One of the more powerful features of the Perl language is how associative arrays can be created and manipulated. The next example creates a two-element array and assigns values to it:

 $employee("name")="Fred";  $employee("age")=29; 

To get a listing of the keys contained in an array, use the keys function. For instance:

 @lst = keys(%employee);  #lst now equals ("name", "age") 

Alternatively, if you wanted to list the values stored in array, you could use the values function. For instance:

 @lst = values(%employee);  #lst now equals ("Fred", 29) 

If you want to return both the key and value pairs together, the each function can be used. This function is meant to be used inside of a loop, and on each successive call, it returns the next key/value pair. For instance:

 while (($name, $age) = each(%employee)) {      #some code goes here  } 

To remove an element from an associative array, use the delete function, as in the following:

 $employee("name")="Fred";  $employee("age")=29;  $employee("shoesize")=10;  #The employee array is 3 elements wide  delete $employee("shoesize");  #Now just 2 
Array Access Functions

In Perl, array numbering begins at 0 and proceeds sequentially for every element contained. Lists of elements can be specified by using a comma-separated list. Negative numbers refer to array elements beginning at the end of the element list. The following is a brief listing of examples:

 @lst=("one", "two", "three", "four");  #Set tmp variable to 'one'  $tmp=$lst[0];  #Set tmp to 'two' and 'four'  $tmp=$lst[1,3];  #Set tmp to 'four'  $tmp=$lst[-1]; 

A common use of arrays is as a queue to hold information. Queues typically need to have elements removed or added in a predictable and specified manner. Perl contains several functions that assist with this: pop , push , shift , and unshift . The pop and push functions work on the right side of an array, and shift and unshift process on the left side.

For example:

 @queue=(54,123,65643);  #Return and Remove 65643  $myval=pop(@queue);  #Add 111 to queue  push(@queue, 111);  #  #Return and Remove 54  $myval=shift(@queue);  #Add 222 to left side  unshift(@queue, 222); 

To reverse or reorder the list of elements, use the reverse or sort function, as in the following:

 @lst=(10,1,5);  @lst=reverse(@lst); #Now lst = (5,1,10)  @lst=sort(@lst); #Now lst = (1,5,10) 
Perl and Regular Expressions

One of the things that has made Perl so widely used is its use of regular expressions ( regex ). Essentially, regular expressions are a method to match patterns between a supplied template and the source text. A full explanation of regex is beyond the scope of this book; however, Table 11.1 and Table 11.2 provide some examples.

Table 11.1. Characters Used for Pattern Matching in regex

regex Pattern

Description

/anytext/

The text anytext is specifically searched for.

.

Represents any character.

*

Zero or more of the preceding character.

+

One or more of the preceding character.

?

Any single character.

[^anytext]

Doesn't contain an anytext .

^anytext

Starts with the text anytext .

Table 11.2. Example Search and Results

Source Text

regex Template

Notes/Matches

PostgreSQL is good

/POSTGRESQL/

No match. Case sensitive.

/POSTGRESQL/i

Match. Case insensitive.

/eS/

eS match (PostgreSQL).

/ /

Match (space found).

/[efgh]ood/

Match (good).

/[e-h]ood/

Match. Same as above. (good).

/Postgre[^SQL]/

No match. Negated "SQL".

/^P/

Match. Starts "P" (PostgeSQL).

/g..d/

Match (good).

/g.*d/

Match (good).

/goo+/

No match. Doesn't end in "o".

/i*s/

Match. Zero match of "i" (is).

/i+s/

No match (is).

/PostgreSQ?/

Match (PostgreSQL).

!/good/

No match. Negated "good".

PL/Perl Language Specifics

The basic format of the PL/Perl language is as follows:

 CREATE FUNCTION name (Arg1 [, ArgN]) RETURNS type AS '       Return $_[0]'  LANGUAGE 'plperl'; 
Escaping Characters

As with PL/pgSQL and PL/Tcl, it is important to remember that quoted strings inside of a PL/Perl function need to be properly escaped.

Use of the Perl functions q[] , qq[] , and qw[] can assist in creating properly escaped variable-substitution sequences.

Variable Substitution

By default, variables are passed to the underlying Perl function as "$_". This variable is the default Perl namespace when no explicit variable has been specified, and consequently, this is the namespace that inherits PL/Perl variables . For instance:

 CREATE FUNCTION getproduct(INTEGER, INTEGER) RETURNS INTEGER AS '       $newval=$_[0] * $_[1];       return $newval;'  LANGUAGE 'plperl'; 

Additionally, entire tuples can be passed to a PL/Perl function. Within the PL/Perl code, the keys of the associative array are the field names from the passed tuple. Obviously, the values of the associative array hold the field data. For instance:

 CREATE FUNCTION citystate(employee) RETURNS INTEGER AS '       $empl = shift;       return $empl->{"city"} + $empl->{"state"};  LANGUAGE 'plperl'; 
Notes

When installing PL/Perl ”whether at compile time or after ”it is required that the Perl language and associated libraries exist on the target system for installation to be successful. Moreover, the shared library version of libperl (that is, libperl.so ) should be present so that PostgreSQL can have access to it.

I l @ ve RuBoard


PostgreSQL Essential Reference
PostgreSQL Essential Reference
ISBN: 0735711216
EAN: 2147483647
Year: 2001
Pages: 118
Authors: Barry Stinson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net