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.

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.