pl/perl thoughts

I haven't got past the thinking stage yet, and a few things have held
up my progress elsewhere. The one possibly productive thought I have
to share with you is this: it is probably overkill to have a separate
Safe container for each plperl function. I don't see any reason that
they shouldn't all live in one Safe container. Then they could share
data and indeed some preloaded functions without doing anything special.

Thus the following perl contained in plperl.c and executed on
interpreter startup:

require Safe; SPI::bootstrap();
sub ::mksafefunc { my $x = new Safe;
$x->permit_only(':default');$x->permit(':base_math');
$x->share(qw[&elog &DEBUG &LOG &INFO &NOTICE &WARNING
&ERROR]);
return $x->reval(qq[sub { $_[0] }]); }
sub ::mkunsafefunc {return eval(qq[ sub { $_[0] } ]); }

would become something like:

require Safe; SPI::bootstrap();
use vars qw($PLContainer); $PLContainer = new
Safe("PLPerl");

$PLContainer->permit_only(':default');$PLContainer->permit(':base_math');
$PLContainer->share(qw[&elog &DEBUG &LOG &INFO &NOTICE
&WARNING &ERROR]);
sub ::mksafefunc { return $PLContainer->reval(qq[sub {
$_[0] }]); }
sub ::mkunsafefunc {return eval(qq[ sub { $_[0] } ]); }

Now you could do something like this:

create function myplperlfuncs() returns int language plperl is '
$datavar = "foo";
$funcvar = sub { return "bar"; };
return 1;
';

create function f1 () returns text language plperl as '
return $datavar;
';

create function f2() returns text language plperl as '
return &$funcvar();
';

At the start of your session you would issue "select myplperlfuncs();"
to preload the values, and thereafter you could call f1() and f2()
quite happily.

It's actually a bit of a pity that we don't have provision for a per
database exec on startup procedure, which could handle this more
elegantly (i.e. we would register myplperlfuncs() as something to be
run on startup, so the user wouldn't have to worry at all about it.)

I have not tested any of this - it is still straight out of my head.

As others have noted, the biggest need is for full SPI access. I don't
think this is hard - just a fair bit of work. After that it would be
very nice to have a DBI type handle so that programmers used to doing
things the DBI way (and what perl programmer isn't?) will feel right
at home with plperl. In most cases I guess that would be a thin layer
over the SPI stuff. Alternatively, you could hide the SPI access and
just make the DBI handle visible - it would still be mostly calling
SPI under the hood, of course. As they say in the perl world,
TIMTOWTDI. Providing a DBI handle would also square with what happens
in the Java world, where you can write server side methods that access
the database via a JDBC interface.