PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl

There was some discussion a few weeks ago about inter-stored-procedure
calling from PL/Perl.

I thought I'd post the documentation (and tests) for a module I'm
working on to simplify calling SQL functions from PL/Perl.

Here are some real-world examples (not the best code, but genuine
use-cases):

Calling a function that returns a single value (single column):
Old:
$count_sql = spi_exec_query("SELECT * FROM tl_activity_stats_sql('"
. &$to_array(statistic=> $stat, person_id => $lead->{person_id})
. "'::text[], $debug)")->{rows}->[0]->{tl_activity_stats_sql};

New:
$count_sql = call('tl_activity_stats_sql(text[],int)',
[ statistic=> $stat, person_id => $lead->{person_id} ], $debug);

The call() function recognizes the [] in the signature and knows that it
needs to handle the corresponding argument being an array reference.

Calling a function that returns a single record (multiple columns):
Old:
$stat_sql = "SELECT * FROM tl_priority_stats($lead->{id}, $debug)";
$stat_sth = spi_query($stat_sql);
$stats = spi_fetchrow($stat_sth);
New:
$stats = call('tl_priority_stats(int,int)', $lead->{id}, $debug);

Calling a function that returns multiple rows of a single value:
Old:
my $sql = "SELECT * FROM tl_domain_mlx_area_ids($mlx_board_id, $domain_id, $debug)";
my $sth = spi_query($sql);
while( my $row = spi_fetchrow($sth) ) {
push(@mlx_area_ids, $row->{tl_domain_mlx_area_ids});
}
New:
@mlx_area_ids = call('tl_domain_mlx_area_ids(int,int,int)', $mlx_board_id, $domain_id, $debug);

I've appended the POD documentation and attached the (rough but working)
test script.

I plan to release the module to CPAN in the next week or so.

I'd greatly appreciate any feedback.

Tim.

=head1 NAME

PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl

=head1 SYNOPSIS

use PostgreSQL::PLPerl::Call qw(call);

Returning single-row single-column values:

$pi = call('pi()'); # 3.14159265358979

$net = call('network(inet)', '192.168.1.5/24'); # '192.168.1.0/24';

$seqn = call('nextval(regclass)', $sequence_name);

$dims = call('array_dims(text[])', '{a,b,c}'); # '[1:3]'

# array arguments can be perl array references:
$ary = call('array_cat(int[], int[])', [1,2,3], [2,1]); # '{1,2,3,2,1}'

Returning multi-row single-column values:

@ary = call('generate_series(int,int)', 10, 15); # (10,11,12,13,14,15)

Returning single-row multi-column values:

# assuming create function func(int) returns table (r1 text, r2 int) ...
$row = call('func(int)', 42); # returns hash ref { r1=>..., r2=>... }

Returning multi-row multi-column values:

@rows = call('pg_get_keywords()'); # ({...}, {...}, ...)

=head1 DESCRIPTION

The C<call> function provides a simple effcicient way to call SQL functions
from PostgreSQL PL/Perl code.

The first parameter is a I<signature> that specifies the name of the function
to call and then, in parenthesis, the types of any arguments as a comma
separated list. For example:

'pi()'
'generate_series(int,int)'
'array_cat(int[], int[])'

The types specify how the I<arguments> to the call should be interpreted.
They don't have to exactly match the types used to declare the function you're
calling.

Any further parameters are used as arguments to the function being called.

=head2 Array Arguments

The argument value corresponding to a type that contains 'C<[]>' can be a
string formated as an array literal, or a reference to a perl array. In the
later case the array reference is automatically converted into an array literal
using the C<encode_array_literal()> function.

=head2 Varadic Functions

Functions with C<varadic> arguments can be called with a fixed number of
arguments by repeating the type name in the signature the same number of times.
For example, given:

create function vary(VARADIC int[]) as ...

you can call that function with three arguments using:

call('vary(int,int,int)', $int1, $int2, $int3);

Alternatively, you can append the string 'C<...>' to the last type in the
signature to indicate that the argument is varadic. For example:

call('vary(int...)', @ints);

=head2 Results

The C<call()> function processes return values in one of four ways depending on
two criteria: single column vs. multi-column results, and list context vs scalar context.

If the results contain a single column with the same name as the function that
was called, then those values are extracted returned directly. This makes
simple calls very simple:

@ary = call('generate_series(int,int)', 10, 15); # (10,11,12,13,14,15)

Otherwise, the rows are returned as references to hashes:

@rows = call('pg_get_keywords()'); # ({...}, {...}, ...)

If the C<call()> function was executed in list context then all the values/rows
are returned, as shown above.

If the function was executed in scalar context then an exception will be thrown
if more than one row is returned. For example:

$foo = call('generate_series(int,int)', 10, 10); # 10
$bar = call('generate_series(int,int)', 10, 11); # dies

=head2 Performance

Internally C<call()> uses C<spi_prepare()> to create a plan to execute the
function with the typed arguments.

The plan is cached using the call 'signature' as the key. (Minor variations in
the signature will still reuse the same plan because an extra cache entry is
created using a 'normalized' signature.)

=head2 Limitations and Caveats

Requires PostgreSQL 9.0 or later.

Types that contain a comma can't be used in the call signature. That's not a
problem in practice as it only affects 'C<numeric(p,s)>' and 'C<decimal(p,s)>'
and the 'C<,s>' part isn't needed. Typically the 'C<(p,s)>' portion isn't used in
signatures.

The return value of functions that have a C<void> return type should not be
relied upon.

=cut