

Chapter 37. PL/Perl - Perl Procedural Language




Note: Users of source packages must specially enable the build of PL/Perl during the installation process. (Refer to Chapter 14 for more information.) Users of binary packages might find PL/Perl in a separate subpackage.

37.1. PL/Perl Functions and Arguments


    # PL/Perl function body
$$ LANGUAGE plperl;

The body of the function is ordinary Perl code.

The syntax of the CREATE FUNCTION command requires the function body to be written as a string constant. It is usually most convenient to use dollar quoting (see Section for the string constant. If you choose to use regular single-quoted string constant syntax, you must escape single quote marks (') and backslashes (\) used in the body of the function, typically by doubling them (see Section



    return $_[1];
$$ LANGUAGE plperl;


    my ($a,$b) = @_;
    if (! defined $a) {
        if (! defined $b) { return undef; }
        return $b;
    return $b;
$$ LANGUAGE plperl;



CREATE TABLE employee (
    name text,
    basesalary integer,
    bonus integer

CREATE FUNCTION empcomp(employee) RETURNS integer AS $$
    my ($emp) = @_;
$$ LANGUAGE plperl;

SELECT name, empcomp(employee.*) FROM employee;



$$ LANGUAGE plperl;

SELECT * FROM perl_row();

Any columns in the declared result data type that are not present in the hash will be returned as NULLs.

PL/Perl functions can also return sets of either scalar or composite types. To do this, return a reference to an array that contains either scalars or references to hashes, respectively. Here are some simple examples:

return [0..$_[0]];
$$ LANGUAGE plperl;

SELECT * FROM perl_set_int(5);

    return [
$$  LANGUAGE plperl;

SELECT * FROM perl_set();

Note that when you do this, Perl will have to build the entire array in memory; therefore the technique does not scale to very large result sets.

PL/Perl does not currently have full support for domain types: it treats a domain the same as the underlying scalar type. This means that constraints associated with the domain will not be enforced. This is not an issue for function arguments, but it is a hazard if you declare a PL/Perl function as returning a domain type.

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