use perl inside postgres (plperl and plperlu)

In ubuntu you first need to install the package postgresql-plperl-8.3 or whatever postgresversion you are using.

Then you need to create the language for the database you want to use perl in. I did this inside postgres logged in as databaseowner to the database I want to use plperl:

create LANGUAGE plperl;

Now I can create and use simple functions like:

CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
    if ($_[0] > $_[1]) { return $_[0]; }
    return $_[1];
$$ LANGUAGE plperl;

select perl_max(7,5);

This is documented at http://www.postgresql.org/docs/8.3/interactive/plperl-funcs.html

But note that you cannot use certain perl-statements and operations here. You are not allowed to read or save files, you are not allowed to load external modules and so on. This is a security-measure to be able to create plperl-functions that can be used by untrusted user.

If you try you will receive an error like:

ERROR: creation of Perl function “excel2year” failed: ‘require’ trapped by operation mask at line 2.
You have to use untrusted plperl (short plperlu) which is only available to the superuser. (see next chapter)

After you have installed plperl as explained above you can install plperlu which will give you full access to all perl-functions including fileaccess and external modules and whatever is needed to solve complex operations and/or fuck up your system. Be careful what you do. Of course !!

First you need to create the language plperlu

I did this on bash - logged in as postgresql-superuser which is postgres in my case:

postgres@tng:~$ createlang plperlu -d knowledge -U postgres

Then I logged in as superuser to postgres and created my function:

CREATE FUNCTION excel2year (integer) RETURNS integer AS $$
    use DateTime;
    my $dd=$_[0];
    my $d=DateTime->new(year=>1900,month=>1,day=>1);
    $d->add(days=>$dd-2);
    return sprintf("%04u",$d->year);
$$ LANGUAGE plperlu;

PLEASE NOTE THAT THIS UNSAFE PLPERLU-FUNCTIONS STILL CAN BE USED BY ORDINARY USERS TOO !!!

details about his trusted stuff see: http://www.postgresql.org/docs/8.4/interactive/plperl-trusted.html