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

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.

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

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

Types or args?

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

It's what I would expect, but I'm not an authority on this stuff.

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

Yay!

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

IIRC (again, sorry), that's what DBD::Pg does: It checks all the args and turns an array into an SQL array, without regard to specified types.

>> 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 :)

What about `SP->schema::function_name()`? 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';

)

>> 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, @_); }

Yep. Might be nice sugar to just throw in your module anyway.

> 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');

The latter is nice, as then the DBA can specify the name of package/global object.

Best,

David