Rhomber

You may or may not have noticed that you can't simply call PLPerl SPI subroutines from within your module. What happens is you get an error saying cannot find Package::Name::spi_*. This is because the PL/Perl SPI subroutines are not provided as perl CORE functions but rather local subroutines in the main:: package.

What can be done?

You may be tempted (as I was) to pass in an anonymous sub routine reference to your module like so:

return Package::Name->new()->process( sub { return spi_exec_query(@_) } )

This may seem like it would work, but you then run into problems because the spi_* PL/Perl subroutines have a prototype that restricts you from passing in an array. So you end up with code more like:

return Package::Name->new()->process( sub { return spi_exec_query($_[0], $_[1]) } )

Which, as you will agree is just plain ugly. So, to overcome the prototype you can instead call it like this:

return Package::Name->new()->process( sub { return &spi_exec_query(@_) } )

NB: This is the only good example of when to prefix a subroutine with &. But, this is still very ugly, so how can we make this work even nicer?

Elegant solution

Knowing that the SPI subroutines are local within the main:: package we can call them by their fully qualified name from within our package and bypass having to pass in an anonymous subroutine, like so:

main::spi_exec_query('....', '...')

To limit having to call the fully qualified name each and every time we can make an alias to this function like so:

BEGIN { *spi_exec_query = \&main::spi_exec_query }

called then like so:

spi_exec_query('...', '...')

But, then of course you have to do that in every package that you need to access the SPI. So this too is not a really effective solution.

Wrapping it all up

The best option then is to make a convent way to access the SPI, which now because we know how to call it we can do. Let's make a wrapper module:

package Package::Name::PLPerl::SPI;

use strict;

require Exporter;

our $VERSION = '0.01';

our @ISA = qw(Exporter);
our @EXPORT_OK = qw(pg_exec_query pg_prepare pg_exec_prepared);

sub pg_exec_query {
return &::spi_exec_query(@_);
}

sub pg_prepare {
return &::spi_prepare(@_);
}

sub pg_exec_prepared {
return &::exec_prepared(@_);
}

1;

Now if we want to use our wrapper, all we have to do is:

use Package::Name::PLPerl::SPI qw(pg_exec_query);

my $r = pg_exec_query('...', '...');

Neat huh? Which means if you design your code well enough you can PROVIDE the data to your object in a test case and when the code is called from a PLPerl function it can load the data itself. Which makes the code truly unit testable. You could even make your wrapper module aware of the testing environment to return the testing data if you wanted, or something similar.

In any case, keeping all the logic out of the PL/Perl function and inside the modules themselves can only be a good thing.