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;

The error message is: SQL Error [42601]: ERROR: Unable to load DBI.pm into plperl at line 3 BEGIN failed--compilation aborted at line 3 — guhl, Mar 27 '18 at 09:56
Possible duplicate of [can you use libraries in PL/Perl](https://stackoverflow.com/questions/3527843/can-you-use-libraries-in-pl-perl) — melpomene, Mar 27 '18 at 20:52

score 0 · Answer 1 · answered Dec 18 '18 at 17:01

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.

PERL DBI in PL/PERL in POSTGRESQL

1 Answers1