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

On Sat, Feb 13, 2010 at 02:25:48PM -0800, David E. Wheeler wrote:
> On Feb 12, 2010, at 3:10 PM, Tim Bunce wrote:
>
> > I've appended the POD documentation and attached the (rough but working)
> > test script.
> >
> > I plan to release the module to CPAN in the next week or so.
> >
> > I'd greatly appreciate any feedback.
>
> I like the idea overall, and anything that can simplify the interface is more than welcome. However:
>
> * I'd rather not have to specify a signature for a non-polymorphic function.

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)
b) treat it the same as call('foo(text...)', @args)
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'd like to be able to use Perl code to call the functions as discussed
> previously, something like:
>
> my $count_sql = SP->tl_activity_stats_sql(
> [ statistic => $stat, person_id => $pid ],
> $debug
> );
>
> For a Polymorphic function, perhaps it could be something like:
>
> my $count = SP->call(
> tl_activity_stats_sql => [qw(text[] int)],
> [ statistic => $stat, person_id => $pid ],
> $debug
> );
>
> The advantage here is that I'm not writing functions inside strings,

Umm,
tl_activity_stats_sql => [qw(text[] int)]

seems to me longer and rather less visually appealing than

'tl_activity_stats_sql(text[], int)'

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

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)

You could always add a trivial SP::AUTOLOAD wrapper function to your
plperl.on_init code :)

> Anyway, That's just interface arguing. The overall idea is sound and
> very much appreciated.

Thanks!

Tim.