Matt Sergeant wrote:
>
> On 28 Feb 2002, Michael A Nachbaur wrote:
>
> > > One thing that Pg offers that nobody else does is Perl stored
> procedures.
> > > I haven't had the opportunity to try them yet, but it sounds very cool
> > > indeed to be able to write SPs in Perl. Has anyone here used
> those yet?
> >
> > I've toyed with them a bit, but for security purposes, the verison of
> > perl that is linked in with Pg cannot access external files or system
> > calls. As a result, you can't "require" or "use" any external
> > libraries. Also, another real bummer, is you can't actually invoke any
> > SQL queries from within a perl stored procedure. All you can do is
> > things like regex's and other stuff on your input arguments.
>
> There's a module on CPAN allowing you to do SQL from within PL SP's.
>
It's DBD::PgSPI. It's not quite at the level where you can drop external DBI
based scripts into the SP and instantly reap the efficiency benefits but the
changes are generally minimal (use q{} though as escaping single quotes is a
pain). Pg doesn't support nested transactions so autocommit has to be off
and no $dbh->commit() stuff.
What you're looking for to get outside of the Safe.pm sandbox is plperlu
(plperlu was only available as a patch to 7.1.x but is included in 7.2). The
untrusted version allows you to do anything Perl can do running under the Pg
user account; as such it's only available to superusers.
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/plperl.html
Unfortunately DBD::Pg and DBD::PgSPI seem to be rapidly falling behind in
terms of updates.
I've been patching my local copy of DBD::Pg to the best of my ability,
unfortunately my C knowledge is rather limited. Myself and others who've
tried contacting the mainter with patches haven't recieved replies. Not sure
if Edmund is just really busy or no longer maintaining it...? Rather
unfortunate either way, especially with the improvements to the interface
libraries seen in 7.2.
