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';
The creation of the function will succeed, but executing it will not.