Unsupported versions: 7.0
This documentation is for an unsupported version of PostgreSQL.
You may want to view the same page for the current version, or one of the other supported versions listed above instead.

Using PL/Perl

Assume you have the following table:

    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;