PERL DBI in PL/PERL in POSTGRESQL

Question

Can I use DBI in a pl/perl function created in Postgresql to select any foreign database?

Im getting the error: Unable to laod DBI.pm into plperl

(I know that there are oracle foreign data wrappers, but I just need to store the resultset of a select statement fired against Oracle, MSSQL or PG and store it in Postgres.)

Here is my function (just with the connect string at the moment):

CREATE OR REPLACE FUNCTION sel_ora()
 RETURNS VOID AS $$

use DBI;

my $db = DBI->connect( "dbi:Oracle:DBKUNDEN", "stadl", "sysadm" )

    || die( $DBI::errstr . "\n" );


$$ LANGUAGE plperl;

Answer 1

Yes, you can use DBI from within plperl.

Note that for security reasons, plperl restricts access to using perl modules. This is intended for multi-user databases where your postgres users are not trusted.

The solution in plperl is to add a line such as this to your postgresql.conf file:

plperl.on_init = 'use DBI;'

Then DBI will be available within your plperl functions. See docs: https://www.postgresql.org/docs/9.5/plperl-under-the-hood.html

Alternatively, if this security consideration does not apply in your situation, then you can use plperlu (u = unrestricted) instead of plperl. Then you can use any perl module directly from your plperlu code.