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

On Mon, Feb 15, 2010 at 11:52:01AM -0800, David E. Wheeler wrote:
> On Feb 15, 2010, at 2:51 AM, Tim Bunce wrote:
>
> > The signature doesn't just qualify the selection of the function,
> > it also ensures appropriate interpretation of the arguments.
> >
> > I could allow call('foo', @args), which could be written call(foo => @args),
> > but what should that mean in terms of the underlying behaviour?
> >
> > I think there are three practical options:
> > a) treat it the same as call('foo(unknown...)', @args)
>
> I believe that's basically what psql does. It's certainly what DBD::Pg does.

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.

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."

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

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.

> > c) instead of using a cached prepared query, build an SQL statement
> > for every execution, which would naturally have to quote all values:
> > my $args = join ",", map { ::quote_nullable($_) } @_;
> > return ::spi_exec_query("select * from $spname($args)");
> >
> > I suspect there are subtle issues (that I'm unfamilar with) lurking here.
> > I'd appreciate someone with greater understanding spelling out the issues
> > and trade-offs in those options.
>
> I'm pretty sure the implementation doesn't have to declare the types of anything:
>
> sub AUTOLOAD {
> my $self = shift;
> our $AUTOLOAD;
> (my $fn = $AUTOLOAD) =~ s/.*://;
> my $prepared = spi_prepare(
> 'EXECUTE ' . quote_ident($fn) . '('
> . join(', ', ('?') x @_)
> . ')';
> # Cache it and call it.
> }

I'm currently using:

my $placeholders = join ",", map { '$'.$_ } 1..$arity;
my $plan = spi_prepare("select * from $spname($placeholders)", @$arg_types) };

and it turns out that spi_prepare is happy to prepare a statement with
more placeholders than there are types provided.

I'm a little nervous of relying on that undocumented behaviour.
Hopefully someone can clarify if that's expected behaviour.

So, anyway, I've now extended the code so the parenthesis and types
aren't needed. Thanks for prompting the investigation :)

> > Umm,
> > tl_activity_stats_sql => [qw(text[] int)]
> >
> > seems to me longer and rather less visually appealing than
> >
> > 'tl_activity_stats_sql(text[], int)'
>
> That would work, too. But either way, having to specify the signature
> would be the exception rather than the rule. You'd only need to do it
> when calling a polymorphic function with the same number of arguments
> as another polymorphic function.

[Tick]

> >> and only provide the signature when I need to disambiguate between
> >> polymorphic variants.
> >
> > Or need to qualify the type of the argument for some other reason, like
> > passing an array reference.
>
> I don't think it's necessary. I mean, if you're passed an array, you
> should of course pass it to PostgreSQL, but it can be anyarray.

Sure, you can pass an array in encoded string form, no problem.
But specifying in the signature a type that includes [] enables
you to use a perl array _reference_ and let call() look after
encoding it for you.

I did it that way round, rather than checking all the args for refs on
every call, as it felt safer, more efficient, and more extensible.

> > But perhaps we can agree on one of the options a/b/c above and then
> > this issue will be less relevant. It's not like you'd be saving much
> > typing:
> >
> > call('tl_activity_stats_sql', @args)
> > call(tl_activity_stats_sql => @args)
> > SP->tl_activity_stats_sql(@args)
>
> No, but the latter is more Perlish.

True. You can't specify a schema though, and the 'SP' is somewhat
artificial. Still, I'm coming round to the idea :)

> > You could always add a trivial SP::AUTOLOAD wrapper function to your
> > plperl.on_init code :)
>
> Yeah yeah. I could even put one on CPAN. ;-P

I think it only needs this (untested):

package SP;
sub AUTOLOAD { our $AUTOLOAD =~ s/^SP:://; shift; call($AUTOLOAD, @_); }

I could either add an extra module (PostgreSQL::PLPerl::Call::SP)
or add a fancy import hook like:

use PostgreSQL::PLPerl::Call qw(:AUTOLOAD => 'SP');

Tim.