This page in other versions: Unsupported versions: 7.1

26.2. Using PL/Perl

Assume you have the following table:

    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.

Submit correction

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group