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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers