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

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.

> b) treat it the same as call('foo(text...)', @args)

Probably not a great idea.

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

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

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

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

> 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 But where are you caching planned functions?

Best,

David