Download PostgreSQL 7.1 Programmer`s Guide

Chapter 26. PL/Perl - Perl Procedural Language
26.2. Using PL/Perl
Assume you have the following table:
CREATE TABLE EMPLOYEE (
name text,
basesalary integer,
bonus integer
);
In order to get the total compensation (base + bonus) we could define a function as follows:
CREATE FUNCTION totalcomp(integer, integer) RETURNS integer
AS ’return $_[0] + $_[1]’
LANGUAGE ’plperl’;
Notice that the arguments to the function are passed in @_ as might be expected.
We can now use our function like so:
SELECT name, totalcomp(basesalary, bonus) FROM employee;
But, we can also pass entire tuples to our functions:
CREATE FUNCTION empcomp(employee) RETURNS integer AS ’
my $emp = shift;
return $emp->{’’basesalary’’} + $emp->{’’bonus’’};
’ LANGUAGE ’plperl’;
A tuple is passed as a reference to a hash. The keys are the names of the fields in the tuples. The hash
values are values of the corresponding fields in the tuple.
Tip: Because the function body is passed as an SQL string literal to CREATE FUNCTION you have
to escape single quotes within your Perl source, either by doubling them as shown above, or by
using the extended quoting functions (q[], qq[], qw[]). Backslashes must be escaped by doubling
them.
The new function empcomp can used like:
SELECT name, empcomp(employee) FROM employee;
Here is an example of a function that will not work because file system operations are not allowed for
security reasons:
CREATE FUNCTION badfunc() RETURNS integer AS ’
open(TEMP, ">/tmp/badfile");
print TEMP "Gotcha!\n";
return 1;
’ LANGUAGE ’plperl’;
323