Skip site navigation (1) Skip section navigation (2)

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

From: Tim Bunce <Tim(dot)Bunce(at)pobox(dot)com>
To: "David E(dot) Wheeler" <david(at)kineticode(dot)com>,Richard Huxton <dev(at)archonet(dot)com>
Cc: Tim Bunce <Tim(dot)Bunce(at)pobox(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
Date: 2010-02-15 10:51:14
Message-ID: 20100215105114.GA373@timac.local (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
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,

    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

    call('tl_activity_stats_sql', @args)
    call(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.



In response to


pgsql-hackers by date

Next:From: Greg StarkDate: 2010-02-15 11:19:24
Subject: Re: [COMMITTERS] pgsql: Speed up CREATE DATABASE by deferring the fsyncs until after
Previous:From: Andres FreundDate: 2010-02-15 10:43:36
Subject: Re: idle in txn query cancellation

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group