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

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.