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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
