On Mon, Feb 15, 2010 at 02:58:47PM -0800, David E. Wheeler wrote:
> On Feb 15, 2010, at 2:42 PM, Tim Bunce wrote:
>
> > I've not really looked the the DBD::Pg code much so this seemed like a
> > good excuse... It looks like the default is to call PQprepare() with
> > paramTypes Oid values of 0.
>
> Yes, IIRC, 0 == unknown as far as the server is concerned. It just
> tells the server to resolve it when it can.
An extra source of puzzlement is that the oid of the 'unknown' type is
705 not 0, and the unknown type isn't discussed in the docs (as far as I
could see).
> > http://developer.postgresql.org/pgdocs/postgres/libpq-exec.html says
> > "If paramTypes is NULL, or any particular element in the array is zero,
> > the server assigns a data type to the parameter symbol in the same way
> > it would do for an untyped literal string."
>
> Right, exactly.
>
> > But I don't know if that means it has the same semantics as using
> > 'unknown' as a type to PL/Perl's spi_prepare(). The docs for
> > spi_prepare() don't mention if type parameters are optional or what
> > happens if they're omitted.
> > http://developer.postgresql.org/pgdocs/postgres/plperl-builtins.html#PLPERL-DATABASE
>
> Same as in SQL PREPARE, I'm sure. Ultimately that's what's doing the work,
> IIUC.
>
> > Looking at the code I see spi_prepare() maps the provided arg type names
> > to oids then calls SPI_prepare(). The docs for SPI_prepare() also don't
> > mention if the type parameters are optional or what happens if they're
> > omitted.
> > The docs for the int nargs parameter say "number of input *parameters*"
> > not "number of parameters that Oid *argtypes describes"
> > http://developer.postgresql.org/pgdocs/postgres/spi-spi-prepare.html
> >
> > Guess I need to go and check the current behaviour... see below.
>
> And like maybe a doc patch might be useful.
I would be great if someone who understood
> > I'm currently using:
> >
> > my $placeholders = join ",", map { '$'.$_ } 1..$arity;
> > my $plan = spi_prepare("select * from $spname($placeholders)",
> > @$arg_types) };
>
> Ah, yeah, that's better, but I do think you should use quote_ident() on the
> function name.
That would cause complications if included a schema name. I've opted to
specify that the name used in the signature should be in quoted form if
it needs quoting.
> > and it turns out that spi_prepare is happy to prepare a statement with
> > more placeholders than there are types provided.
>
> Types or args?
These appear to be identical in behaviour:
spi_prepare("select * from foo($1,$2)", 'unknown', 'unknown');
spi_prepare("select * from foo($1,$2)", 'unknown')
spi_prepare("select * from foo($1,$2)")
> > You can't specify a schema though, and the 'SP' is somewhat
> > artificial. Still, I'm coming round to the idea :)
>
> What about `SP->schema::function_name()`?
Wouldn't work unless you'd installed an AUTOLOAD function into each
schema:: package that you wanted to use. (schema->SP::function_name()
could be made to work but that's just too bizzare :)
> Agreed that SP is artificial, but there needs to be some kind of
> handle for AUTOLOAD to wrap itself around. Maybe a singleton object
> instead? (I was kind of thinking of SP as that, anyway:
> use constant SP => 'PostgreSQL::PLPerl';
> )
Something like that is probably best. I've made PostgreSQL::PLPerl::Call
export both &call and &SP where SP is a constant containing the name
of a class (PostgreSQL::PLPerl::Call::SP) that just has an AUTOLOAD.
I've attached the current docs and code.
Thanks for your help David!
Tim.
package PostgreSQL::PLPerl::Call;
=head1 NAME
PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from
PostgreSQL PL/Perl
=head1 SYNOPSIS
use PostgreSQL::PLPerl::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'); # ({...}, {...}, ...)
Alternative method-call syntax:
$pi = SP->pi();
$seqn = SP->nextval($sequence_name);
=head1 DESCRIPTION
The C<call> function provides a simple efficient 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, optionally, the types of the arguments.
Any further parameters are used as argument values for the function being
called.
=head2 Signature
The first parameter is a I<signature> that specifies the name of the function.
The name should be given in the same way it would in an SQL statement, so
if identifier quoting is needed it should be specified in the already quoted
form.
Immediately after the function name, in parenthesis, a comma separated list of
type names can be given. For example:
'pi()'
'generate_series(int,int)'
'array_cat(int[], int[])'
'myschema.myfunc(date, float8)'
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.
You also don't have to specify types for I<all> the arguments, just the
left-most arguments that need types.
=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 Method-call Syntax
An alternative syntax can be used for making calls:
SP->function_name(@args)
For example:
$pi = SP->pi();
$seqn = SP->nextval($sequence_name);
Using this form you can't easily specify a schema name or argument types, and
you can't call varadic functions.
If cases where a signature is needed you might get a somewhat confusing error
message. For example:
SP->generate_series(10,20);
fails with the error "there is no parameter $1". The underlying problem is that
C<generate_series> is a polymorphic function: different versions of the
function are executed depending on the type of the arguments.
==head2 Wrapping and Currying
It's simple to wrap a call into an anonymous subroutine and pass that code
reference around. For example:
$nextval_fn = sub { SP->nextval(@_) };
...
$val = $nextval_fn->($sequence_name);
or
$some_func = sub { call('some_func(int, date[], int)', @_) };
...
$val = $some_func->($foo, \...@dates, $debug);
You can take this approach further by specifying some of the arguments in the
anonymous subroutine so they don't all have to be provided in the call:
$some_func = sub { call('some_func(int, date[], int)', $foo, shift, $debug)
};
...
$val = $some_func->(\...@dates);
=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 and 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
If you only want the first result you can use list context;
($bar) = call('generate_series(int,int)', 10, 11);
$bar = (call('generate_series(int,int)', 10, 11))[0];
=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.
For varadic functions, separate plans are created and cached for each distinct
number of arguments the function is called with.
=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.
Functions with a varadic argument can't be called with no values for that
argument. You'll get a "function ... does not exist" error. This appears to be
a PostgreSQL limitation.
The return value of functions that have a C<void> return type should not be
relied upon, naturally.
=cut
use strict;
use warnings;
use Exporter;
use Carp;
our @ISA = qw(Exporter);
our @EXPORT = qw(call SP);
my %sig_cache;
our $debug = 0;
# encapsulated package to provide an AUTOLOAD interface to call()
use constant SP => do {
package PostgreSQL::PLPerl::Call::SP;
sub AUTOLOAD {
#(my $function = our $AUTOLOAD) =~ s/.*:://;
our $AUTOLOAD =~ s/.*:://;
shift;
return PostgreSQL::PLPerl::Call::call($AUTOLOAD, @_);
}
__PACKAGE__;
};
sub call {
my $sig = shift;
my $arity = scalar @_; # argument count to handle varadic subs
my $how = $sig_cache{"$sig.$arity"} ||= do {
# get a normalized signature to recheck the cache with
# and also extract the SP name and argument types
my ($stdsig, $fullspname, $spname, $arg_types) = parse_signature($sig,
$arity)
or croak "Can't parse '$sig'";
warn "parsed call($sig) => $stdsig\n"
if $debug;
# recheck the cache with with the normalized signature
$sig_cache{"$stdsig.$arity"} ||= [ # else a new entry (for both caches)
$spname,
scalar mk_process_args($arg_types),
scalar mk_process_call($fullspname, $arity, $arg_types),
$fullspname,
$stdsig,
];
};
my ($spname, $prepargs, $callsub) = @$how;
my $rv = $callsub->( $prepargs ? $prepargs->(@_) : @_ );
my $rows = $rv->{rows};
my $row1 = $rows->[0] # peek at first row
or return; # no row: undef in scalar context else empty list
my $is_single_column = (keys %$row1 == 1 and exists $row1->{$spname});
if (wantarray) { # list context - all rows
return map { $_->{$spname} } @$rows if $is_single_column;
return @$rows;
}
elsif (defined wantarray) { # scalar context - single row
croak "$sig was called in scalar context but returned more than one row"
if @$rows > 1;
return $row1->{$spname} if $is_single_column;
return $row1;
}
# else void context - nothing to do
return;
}
sub parse_signature {
my ($sig, $arity) = @_;
# extract types from signature, if any
my $arg_types;
if ($sig =~ s/\s*\((.*?)\)\s*$//) {
$arg_types = [ split(/\s*,\s*/, lc($1), -1) ];
s/^\s+// for @$arg_types;
s/\s+$// for @$arg_types;
# if varadic, replace '...' marker with the appropriate number
# of copies of the preceding type name
if (@$arg_types and $arg_types->[-1] =~ s/\s*\.\.\.//) {
my $varadic_type = pop @$arg_types;
push @$arg_types, $varadic_type
until @$arg_types >= $arity;
}
}
# the full name is what's left in sig
my $fullspname = $sig;
(my $spname = $fullspname) =~ s/.*\.//; # remove schema, if any
# compose a normalized signature
my $stdsig = "$fullspname".
($arg_types ? "(".join(",",@$arg_types).")" : "");
return ($stdsig, $fullspname, $spname, $arg_types);
}
sub mk_process_args {
my ($arg_types) = @_;
return undef unless $arg_types;
# return a closure that pre-processes the arguments of the call
# else undef if no argument pre-processing is required
my $hooks;
my $i = 0;
for my $type (@$arg_types) {
if ($type =~ /\[/) { # ARRAY
$hooks->{$i} = sub { return ::encode_array_literal(shift) };
}
++$i;
}
return undef unless $hooks;
my $sub = sub {
my @args = @_;
while ( my ($argidx, $preproc) = each %$hooks ) {
$args[$argidx] = $preproc->($args[$argidx]);
}
return @args;
};
return $sub;
}
sub mk_process_call {
my ($spname, $arity, $arg_types) = @_;
# return a closure that will execute the query and return result ref
my $placeholders = join ",", map { '$'.$_ } 1..$arity;
my $sql = "select * from $spname($placeholders)";
my $plan = eval { ::spi_prepare($sql, $arg_types ? @$arg_types : ()) };
if ($@) { # internal error, should never happen
chomp $@;
croak "$@ while preparing $sql";
}
my $sub = sub {
# XXX need to catch exceptions from here and rethrow using croak
# to appear to come from the callers location (outside this package)
warn "calling $sql(@_) [...@{$arg_types||[]}]"
if $debug;
return ::spi_exec_prepared($plan, @_)
};
return $sub;
}
1;
# vim: ts=8:sw=4:sts=4:et
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
