Skip site navigation (1) Skip section navigation (2)

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

From: Tim Bunce <Tim(dot)Bunce(at)pobox(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Cc: Tim Bunce <Tim(dot)Bunce(at)pobox(dot)com>
Subject: PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
Date: 2010-02-12 23:10:15
Message-ID: 20100212231015.GS373@timac.local (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
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

Calling a function that returns a single value (single column):
    $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};
    $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):
    $stat_sql = "SELECT * FROM tl_priority_stats($lead->{id}, $debug)";
    $stat_sth = spi_query($stat_sql);
    $stats = spi_fetchrow($stat_sth);
    $stats = call('tl_priority_stats(int,int)', $lead->{id}, $debug);

Calling a function that returns multiple rows of a single value:
    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});
    @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.


=head1 NAME

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


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

Returning single-row single-column values:

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

    $net = call('network(inet)', ''); # '';

    $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()'); # ({...}, {...}, ...)


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:

    '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

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

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



pgsql-hackers by date

Next:From: Tim BunceDate: 2010-02-12 23:14:31
Subject: Re: PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
Previous:From: Jeff DavisDate: 2010-02-12 22:40:00
Subject: Re: Confusion over Python drivers {license}

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group