Database Reference
In-Depth Information
PL/Perl
The next language in the list of native languages of PostgreSQL is PL/Perl that also
incorporates features to control the low of the program.
Installing PL/Perl
Here, the
CREATE EXTENSION
method will be used by executing the following
statement via the
psql
utility:
warehouse_db=# CREATE EXTENSION plperl;
Writing functions in PL/Perl
You can write a function in PL/Perl by following its simple syntax, as shown:
CREATE OR REPLACE FUNCTION name (arguments) RETURNS return-type AS
$$
# PL/Perl body(Perl code)
$$ LANGUAGE plperl;
You can pass arguments to a PL/Perl function, and they can be
accessed inside the function with the
@_
array and can return
results using
return
.
Let's write a function using the preceding syntax. The
test_perl()
function will
simply return the
txt
value variable to a client program in the following manner:
warehouse_db=# CREATE FUNCTION test_perl() RETURNS text AS $$
$txt='PostgreSQL Rocks !';
return $txt;
$$ LANGUAGE plperl;
Handling arguments with PL/Perl
Now, we will pass arguments to a PL/Perl function and access them. The
perl_
arguments()
function takes input from three arguments, executes a loop whose
iteration depends upon the number of arguments (three times in this example),
and calculates the sum of the
a
variable and the three integer arguments passed
to the function:
warehouse_db=# CREATE OR REPLACE FUNCTION perl_arguments(INTEGER,
INTEGER, INTEGER)
RETURNS INTEGER AS $$
$a = 1;
foreach $argument (@_){
