Using PL/Perl

Assume you have the following table:

CREATE TABLE EMPLOYEE (
    name text,
    basesalary int4,
    bonus int4 );
    

In order to get the total compensation (base + bonus) we could define a function as follows:

CREATE FUNCTION totalcomp(int4, int4) RETURNS int4
    AS 'return $_[0] + $_[1]'
    LANGUAGE 'plperl';
    

Note that the arguments are passed to the function in @_ as might be expected. Also, because of the quoting rules for the SQL creating the function, you may find yourself using the extended quoting functions (qq[], q[], qw[]) more often that you are used to.

We may now use our function like so:

SELECT name, totalcomp(basesalary, bonus) from employee
    

But, we can also pass entire tuples to our function:

CREATE FUNCTION empcomp(employee) returns int4
    AS 'my $emp = shift;
        return $emp->{'basesalary'} + $emp->{'bonus'};'
    LANGUAGE 'plperl';
    

A tuple is passed as a reference to hash. The keys are the names of fields in the tuples. The values are values of the corresponding field in the tuple.

The new function empcomp can used like:

SELECT name, empcomp(employee) from employee;