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

There was some discussion a few weeks ago about inter-stored-procedure
calling from PL/Perl.

I thought I'd post the documentation (and tests) for a module I'm
working on to simplify calling SQL functions from PL/Perl.

Here are some real-world examples (not the best code, but genuine
use-cases):

Calling a function that returns a single value (single column):
Old:
$count_sql = spi_exec_query("SELECT * FROM tl_activity_stats_sql('"
. &$to_array(statistic=> $stat, person_id => $lead->{person_id})
. "'::text[], $debug)")->{rows}->[0]->{tl_activity_stats_sql};

New:
$count_sql = call('tl_activity_stats_sql(text[],int)',
[ statistic=> $stat, person_id => $lead->{person_id} ], $debug);

The call() function recognizes the [] in the signature and knows that it
needs to handle the corresponding argument being an array reference.

Calling a function that returns a single record (multiple columns):
Old:
$stat_sql = "SELECT * FROM tl_priority_stats($lead->{id}, $debug)";
$stat_sth = spi_query($stat_sql);
$stats = spi_fetchrow($stat_sth);
New:
$stats = call('tl_priority_stats(int,int)', $lead->{id}, $debug);

Calling a function that returns multiple rows of a single value:
Old:
my $sql = "SELECT * FROM tl_domain_mlx_area_ids($mlx_board_id, $domain_id, $debug)";
my $sth = spi_query($sql);
while( my $row = spi_fetchrow($sth) ) {
push(@mlx_area_ids, $row->{tl_domain_mlx_area_ids});
}
New:
@mlx_area_ids = call('tl_domain_mlx_area_ids(int,int,int)', $mlx_board_id, $domain_id, $debug);

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.

Tim.

=head1 NAME

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

=head1 SYNOPSIS

use PostgreSQL::PLPerl::Call qw(call);

Returning single-row single-column values:

$pi = call('pi()'); # 3.14159265358979

$net = call('network(inet)', '192.168.1.5/24'); # '192.168.1.0/24';

$seqn = call('nextval(regclass)', $sequence_name);

$dims = call('array_dims(text[])', '{a,b,c}'); # '[1:3]'

# array arguments can be perl array references:
$ary = call('array_cat(int[], int[])', [1,2,3], [2,1]); # '{1,2,3,2,1}'

Returning multi-row single-column values:

@ary = call('generate_series(int,int)', 10, 15); # (10,11,12,13,14,15)

Returning single-row multi-column values:

# assuming create function func(int) returns table (r1 text, r2 int) ...
$row = call('func(int)', 42); # returns hash ref { r1=>..., r2=>... }

Returning multi-row multi-column values:

@rows = call('pg_get_keywords()'); # ({...}, {...}, ...)

=head1 DESCRIPTION

The C<call> function provides a simple effcicient way to call SQL functions
from PostgreSQL PL/Perl code.

The first parameter is a I<signature> that specifies the name of the function
to call and then, in parenthesis, the types of any arguments as a comma
separated list. For example:

'pi()'
'generate_series(int,int)'
'array_cat(int[], int[])'

The types specify how the I<arguments> to the call should be interpreted.
They don't have to exactly match the types used to declare the function you're
calling.

Any further parameters are used as arguments to the function being called.

=head2 Array Arguments

The argument value corresponding to a type that contains 'C<[]>' can be a
string formated as an array literal, or a reference to a perl array. In the
later case the array reference is automatically converted into an array literal
using the C<encode_array_literal()> function.

=head2 Varadic Functions

Functions with C<varadic> arguments can be called with a fixed number of
arguments by repeating the type name in the signature the same number of times.
For example, given:

create function vary(VARADIC int[]) as ...

you can call that function with three arguments using:

call('vary(int,int,int)', $int1, $int2, $int3);

Alternatively, you can append the string 'C<...>' to the last type in the
signature to indicate that the argument is varadic. For example:

call('vary(int...)', @ints);

=head2 Results

The C<call()> function processes return values in one of four ways depending on
two criteria: single column vs. multi-column results, and list context vs scalar context.

If the results contain a single column with the same name as the function that
was called, then those values are extracted returned directly. This makes
simple calls very simple:

@ary = call('generate_series(int,int)', 10, 15); # (10,11,12,13,14,15)

Otherwise, the rows are returned as references to hashes:

@rows = call('pg_get_keywords()'); # ({...}, {...}, ...)

If the C<call()> function was executed in list context then all the values/rows
are returned, as shown above.

If the function was executed in scalar context then an exception will be thrown
if more than one row is returned. For example:

$foo = call('generate_series(int,int)', 10, 10); # 10
$bar = call('generate_series(int,int)', 10, 11); # dies

=head2 Performance

Internally C<call()> uses C<spi_prepare()> to create a plan to execute the
function with the typed arguments.

The plan is cached using the call 'signature' as the key. (Minor variations in
the signature will still reuse the same plan because an extra cache entry is
created using a 'normalized' signature.)

=head2 Limitations and Caveats

Requires PostgreSQL 9.0 or later.

Types that contain a comma can't be used in the call signature. That's not a
problem in practice as it only affects 'C<numeric(p,s)>' and 'C<decimal(p,s)>'
and the 'C<,s>' part isn't needed. Typically the 'C<(p,s)>' portion isn't used in
signatures.

The return value of functions that have a C<void> return type should not be
relied upon.

=cut

On Fri, Feb 12, 2010 at 11:10:15PM +0000, Tim Bunce wrote:
> I've appended the POD documentation and attached the (rough but working)
> test script.

Oops. Here's the test script.

Tim.

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.
* 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, and only provide the signature when I need to disambiguate between polymorphic variants.

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

Best,

David

On 12/02/10 23:10, Tim Bunce wrote:
> There was some discussion a few weeks ago about inter-stored-procedure
> calling from PL/Perl.

> I'd greatly appreciate any feedback.

Looks great.

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

I don't think you show an example with an explicit schema name being
used. Can't hurt to make it obvious.

> $seqn = call('nextval(regclass)', $sequence_name);

Is there any value in having a two-stage interface?

$seq_fn = get_call('nextval(regclass)');
$foo1 = $seq_fn->($seq1);
$foo2 = $seq_fn->($seq2);

--
Richard Huxton
Archonet Ltd

On Mon, Feb 15, 2010 at 07:31:14AM +0000, Richard Huxton wrote:
> On 12/02/10 23:10, Tim Bunce wrote:
> >There was some discussion a few weeks ago about inter-stored-procedure
> >calling from PL/Perl.
>
> >I'd greatly appreciate any feedback.
>
> Looks great.

Thanks!

> >PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
>
> I don't think you show an example with an explicit schema name being
> used. Can't hurt to make it obvious.

Yes, good point. I've added one to the docs and tests. Thanks.

> > $seqn = call('nextval(regclass)', $sequence_name);
>
> Is there any value in having a two-stage interface?
>
> $seq_fn = get_call('nextval(regclass)');
> $foo1 = $seq_fn->($seq1);
> $foo2 = $seq_fn->($seq2);

I don't think there's significant performance value in that.

Perhaps it could be useful to be able to pre-curry a call and
then pass that code ref around, but you can do that trivially
already:

$nextval_fn = sub { call('nextval(regclass)', @_) };
$val = $nextval_fn->($seq1);
or
$nextfoo_fn = sub { call('nextval(regclass)', 'foo_seqn') };
$val = $nextfoo_fn->();

Tim.

On 15/02/10 10:32, Tim Bunce wrote:
> On Mon, Feb 15, 2010 at 07:31:14AM +0000, Richard Huxton wrote:
>>
>> Is there any value in having a two-stage interface?
>>
>> $seq_fn = get_call('nextval(regclass)');
>> $foo1 = $seq_fn->($seq1);
>> $foo2 = $seq_fn->($seq2);
>
> I don't think there's significant performance value in that.
>
> Perhaps it could be useful to be able to pre-curry a call and
> then pass that code ref around, but you can do that trivially
> already:
>
> $nextval_fn = sub { call('nextval(regclass)', @_) };
> $val = $nextval_fn->($seq1);
> or
> $nextfoo_fn = sub { call('nextval(regclass)', 'foo_seqn') };
> $val = $nextfoo_fn->();

Fair enough. Just wondered whether it was worth putting that on your
side of the interface. I'm forced to concede you probably have more
experience in database-related APIs than me :-)

--
Richard Huxton
Archonet Ltd

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.

On Mon, Feb 15, 2010 at 10:42:15AM +0000, Richard Huxton wrote:
> On 15/02/10 10:32, Tim Bunce wrote:
> >On Mon, Feb 15, 2010 at 07:31:14AM +0000, Richard Huxton wrote:
> >>
> >>Is there any value in having a two-stage interface?
> >>
> >> $seq_fn = get_call('nextval(regclass)');
> >> $foo1 = $seq_fn->($seq1);
> >> $foo2 = $seq_fn->($seq2);
> >
> >I don't think there's significant performance value in that.
> >
> >Perhaps it could be useful to be able to pre-curry a call and
> >then pass that code ref around, but you can do that trivially
> >already:
> >
> > $nextval_fn = sub { call('nextval(regclass)', @_) };
> > $val = $nextval_fn->($seq1);
> >or
> > $nextfoo_fn = sub { call('nextval(regclass)', 'foo_seqn') };
> > $val = $nextfoo_fn->();
>
> Fair enough. Just wondered whether it was worth putting that on your
> side of the interface. I'm forced to concede you probably have more
> experience in database-related APIs than me :-)

I've actually very little experience with PostgreSQL! I'm happy to argue
each case on its merits and am certainly open to education and persuasion.

At the moment I don't see enough gain to warrant an additional API.
I am adding the some examples to the docs though. So thanks for that!

Tim.

On Mon, Feb 15, 2010 at 10:51:14AM +0000, Tim Bunce wrote:
> 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.

Just to clarify that... I mean appropriate interpretation not only by
PostgreSQL but also by the call() code knowing which arguments may need
array encoding (without having to check them all on every call).

The signature also makes it easy to refer to functions in other schemas.
Something that a SP->func_name(...) style syntax wouldn't allow.

Tim.

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

On Mon, Feb 15, 2010 at 11:52:01AM -0800, David E. Wheeler wrote:
> 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.

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.

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

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

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.

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

I'm currently using:

my $placeholders = join ",", map { '$'.$_ } 1..$arity;
my $plan = spi_prepare("select * from $spname($placeholders)", @$arg_types) };

and it turns out that spi_prepare is happy to prepare a statement with
more placeholders than there are types provided.

I'm a little nervous of relying on that undocumented behaviour.
Hopefully someone can clarify if that's expected behaviour.

So, anyway, I've now extended the code so the parenthesis and types
aren't needed. Thanks for prompting the investigation :)

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

[Tick]

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

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.

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

True. You can't specify a schema though, and the 'SP' is somewhat
artificial. Still, I'm coming round to the idea :)

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

I think it only needs this (untested):

package SP;
sub AUTOLOAD { our $AUTOLOAD =~ s/^SP:://; shift; call($AUTOLOAD, @_); }

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');

Tim.

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

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.

On Feb 16, 2010, at 4:08 AM, Tim Bunce wrote:

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

Yes, I noticed that, too. Greg, do you know the answer to that?

>>> http://developer.postgresql.org/pgdocs/postgres/libpq-exec.html saysGuess 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

Can any SPI experts chime in here? It seems that the ability to omit types for parameters in spi_prepare() is undocumented. Is that officially okay?

> 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)")

Ah, interesting.

> 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 :)

Maybe SP->schema('public')->function_name()? I kind of like the idea of objects created for specific schemas, though (as in your example). Maybe that, too, is something that could be specified in the `use`statement. Or maybe `SP::schema->function`? That's kind of nice, keeps things encapsulated under SP. You could then do the identifier quoting, too. The downside is that, once loaded, the schema package names would be locked down. If I created a new schema in the connection, SP wouldn't know about it.

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

Cool, thanks!

From the docs:

> Immediately after the function name, in parenthesis, a comma separated list of
> type names can be given. For example:
>
> 'pi()'
> 'generate_series(int,int)'
> 'array_cat(int[], int[])'
> 'myschema.myfunc(date, float8)'

It could also just be 'pi', no?

> Functions with C<varadic> arguments can be called with a fixed number of
> arguments by repeating the type name in the signature the same number of times.

I assume that type names can be omitted her, too, yes?

> $pi = SP->pi();
> $seqn = SP->nextval($sequence_name);
>
> Using this form you can't easily specify a schema name or argument types, and
> you can't call varadic functions.

Why not?

Also, I notice a few `==head`s. I think that's one too many "="s.

> You can take this approach further by specifying some of the arguments in the
> anonymous subroutine so they don't all have to be provided in the call:
>
> $some_func = sub { call('some_func(int, date[], int)', $foo, shift, $debug) };
> ...
> $val = $some_func->(\(at)dates);

Currying! :-)

> If the function was executed in scalar context then an exception will be thrown
> if more than one row is returned. For example:

Someone's going to want an iterator object/cursor. :-P

> For varadic functions, separate plans are created and cached for each distinct
> number of arguments the function is called with.

Why?

> Functions with a varadic argument can't be called with no values for that
> argument. You'll get a "function ... does not exist" error. This appears to be
> a PostgreSQL limitation.

Hrm. Worth enquiring about.

So, is this on GitHub yet? That way I can submit patches.

Best,

David

On Tue, Feb 16, 2010 at 09:11:24AM -0800, David E. Wheeler wrote:
> > 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).
>
> Yes, I noticed that, too. Greg, do you know the answer to that?

My guess is that, semantically, 0 means the datatype is unknown,
whereas 705 means the datatype is known to be type "unknown".

I believe however the backend treats these cases identically (at least,
simple testing doesn't reveal any differences), but they are not the
same.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

On 16/02/10 17:11, David E. Wheeler wrote:
> On Feb 16, 2010, at 4:08 AM, Tim Bunce wrote:
>
>> 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 :)
>
> Maybe SP->schema('public')->function_name()? I kind of like the idea of objects created for specific schemas, though (as in your example). Maybe that, too, is something that could be specified in the `use`statement. Or maybe `SP::schema->function`? That's kind of nice, keeps things encapsulated under SP. You could then do the identifier quoting, too. The downside is that, once loaded, the schema package names would be locked down. If I created a new schema in the connection, SP wouldn't know about it.

Perhaps it would be better to be explicit about what's going on?
SEARCHPATH->function()
SCHEMA('public')->function2()

Or did "SP" mean "Stored Procedure"?

On a (kind of) related note, it might be worthwhile to mention
search_path in the docs and point out it has the same pros/cons as unix
file paths.

--
Richard Huxton
Archonet Ltd

On Feb 16, 2010, at 9:43 AM, Richard Huxton wrote:

> Perhaps it would be better to be explicit about what's going on?
> SEARCHPATH->function()
> SCHEMA('public')->function2()
>
> Or did "SP" mean "Stored Procedure"?

Yes.

> On a (kind of) related note, it might be worthwhile to mention search_path in the docs and point out it has the same pros/cons as unix file paths.

+1. It's a little like file paths and a little like name spaces, without quite being either one.

Best,

David

On 16/02/10 17:51, David E. Wheeler wrote:
> On Feb 16, 2010, at 9:43 AM, Richard Huxton wrote:
>
>> Perhaps it would be better to be explicit about what's going on?
>> SEARCHPATH->function()
>> SCHEMA('public')->function2()
>>
>> Or did "SP" mean "Stored Procedure"?
>
> Yes.

Hmm - might be worth avoiding that in case we get actual
transaction-spanning stored procedures at any point.

--
Richard Huxton
Archonet Ltd

Richard Huxton wrote:
> On 16/02/10 17:51, David E. Wheeler wrote:
> >On Feb 16, 2010, at 9:43 AM, Richard Huxton wrote:
> >
> >>Perhaps it would be better to be explicit about what's going on?
> >> SEARCHPATH->function()
> >> SCHEMA('public')->function2()
> >>
> >>Or did "SP" mean "Stored Procedure"?
> >
> >Yes.
>
> Hmm - might be worth avoiding that in case we get actual
> transaction-spanning stored procedures at any point.

Yeah ... I used to get a lot of questions on the spanish list about
pgAdmin calling some functions "procedures" (just because they returned
void). While it may be technically true, it'd cause trouble if we ever
get around to supporting true procedures.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Tue, Feb 16, 2010 at 09:11:24AM -0800, David E. Wheeler wrote:
> On Feb 16, 2010, at 4:08 AM, Tim Bunce wrote:
>
> From the docs:
>
> > Immediately after the function name, in parenthesis, a comma separated list of
> > type names can be given. For example:
> >
> > 'pi()'
> > 'generate_series(int,int)'
> > 'array_cat(int[], int[])'
> > 'myschema.myfunc(date, float8)'
>
> It could also just be 'pi', no?

Yes. A vestige from when the parens were still needed. Fixed.

> > Functions with C<varadic> arguments can be called with a fixed number of
> > arguments by repeating the type name in the signature the same number of times.
>
> I assume that type names can be omitted her, too, yes?

No, it seems not. You have to either repeat the type name the right number
of times, or use '...', which simply duplicates the type name for you
behind the scenes. I'll clarify that in the docs (and fix all the
places I spelt variadic wrong :)

> > $pi = SP->pi();
> > $seqn = SP->nextval($sequence_name);
> >
> > Using this form you can't easily specify a schema name or argument types,

SP->"schema.func"() doesn't work. ($name="schema.func"; SP->$name() works.)

> > and you can't call varadic functions.
>
> Why not?

Using spi_prepare('select * from variadic_func($1)') the error is "there
is no parameter $1". I suspect calls to varadic functions do need
correct nargs and type information given to the SPI_prepare call.

> Also, I notice a few `==head`s. I think that's one too many "="s.

Fixed. Thanks.

> > For varadic functions, separate plans are created and cached for each distinct
> > number of arguments the function is called with.
>
> Why?

It keeps the code simple and repeat calls fast.

> > Functions with a varadic argument can't be called with no values for that
> > argument. You'll get a "function ... does not exist" error. This appears to be
> > a PostgreSQL limitation.
>
> Hrm. Worth enquiring about.

I found it in the docs: "A parameter marked VARIADIC matches *one* or
more occurrences of its element type".
http://www.postgresql.org/docs/8.4/interactive/xfunc-sql.html

> So, is this on GitHub yet? That way I can submit patches.

I've uploaded PostgreSQL-PLPerl-Call-1.003.tar.gz to CPAN with these
changes. It's in git but not github yet. Maybe soonish.

Tim.

On Feb 16, 2010, at 2:06 PM, Tim Bunce wrote:

>> I assume that type names can be omitted her, too, yes?
>
> No, it seems not. You have to either repeat the type name the right number
> of times, or use '...', which simply duplicates the type name for you
> behind the scenes. I'll clarify that in the docs (and fix all the
> places I spelt variadic wrong :)

Pity.

> SP->"schema.func"() doesn't work. ($name="schema.func"; SP->$name() works.)

Ha! Document that.

>>> For varadic functions, separate plans are created and cached for each distinct
>>> number of arguments the function is called with.
>>
>> Why?
>
> It keeps the code simple and repeat calls fast.

Yes, but if it's a variadic function, I suspect that it won't often be called with the same number of args. So you'd potentially end up caching a lot of extra stuff that would never be used again.

> I found it in the docs: "A parameter marked VARIADIC matches *one* or
> more occurrences of its element type".
> http://www.postgresql.org/docs/8.4/interactive/xfunc-sql.html

Ah, okay, that makes sense.

>> So, is this on GitHub yet? That way I can submit patches.
>
> I've uploaded PostgreSQL-PLPerl-Call-1.003.tar.gz to CPAN with these
> changes. It's in git but not github yet. Maybe soonish.

I saw. I think it might pay to heed Richard's suggestion not to use "SP".

By the way, I think it needs some documentation explaining how to load it inside PL/Perl.

Best,

David

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> On Tue, Feb 16, 2010 at 09:11:24AM -0800, David E. Wheeler wrote:
>>> 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).
>>
>> Yes, I noticed that, too. Greg, do you know the answer to that?

> My guess is that, semantically, 0 means the datatype is unknown,
> whereas 705 means the datatype is known to be type "unknown".

I think the reason the client-side docs recommend using zero is to avoid
having clients know about the unknown type explicitly (in particular, to
discourage people from hardwiring "705" into their code). AFAIR there's
not a lot of difference in terms of what the parser will do with it.

regards, tom lane

On Feb 16, 2010, at 3:01 PM, Tom Lane wrote:

> I think the reason the client-side docs recommend using zero is to avoid
> having clients know about the unknown type explicitly (in particular, to
> discourage people from hardwiring "705" into their code). AFAIR there's
> not a lot of difference in terms of what the parser will do with it.

We should probably get rid of this in DBD::Pg then:

% perl -MDBD::Pg -E 'say DBD::Pg::PG_UNKNOWN'
705

Best,

David

On Tue, Feb 16, 2010 at 02:13:30PM -0800, David E. Wheeler wrote:
> On Feb 16, 2010, at 2:06 PM, Tim Bunce wrote:
>
> >>> For varadic functions, separate plans are created and cached for each distinct
> >>> number of arguments the function is called with.
> >>
> >> Why?
> >
> > It keeps the code simple and repeat calls fast.
>
> Yes, but if it's a variadic function, I suspect that it won't often be
> called with the same number of args. So you'd potentially end up
> caching a lot of extra stuff that would never be used again.

Potentially. Patches welcome!

> >> So, is this on GitHub yet? That way I can submit patches.
> >
> > I've uploaded PostgreSQL-PLPerl-Call-1.003.tar.gz to CPAN with these
> > changes. It's in git but not github yet. Maybe soonish.
>
> I saw. I think it might pay to heed Richard's suggestion not to use "SP".

Umm, perhaps F->funcname(@args), or PG->funcname(@args), or ... ?

Anyone got any better suggestions?

> By the way, I think it needs some documentation explaining how to load it inside PL/Perl.

I thought about that, and started to write it, but dropped it for now.
I'll wait till my "cunning plan" to share code with the Safe compartment
(aka PostgreSQL::PLPerl::Injector) is done then document how call() can
be used in both plperlu and plperl.

Tim.

On Feb 17, 2010, at 4:28 AM, Tim Bunce wrote:

>> Yes, but if it's a variadic function, I suspect that it won't often be
>> called with the same number of args. So you'd potentially end up
>> caching a lot of extra stuff that would never be used again.
>
> Potentially. Patches welcome!

GitHub. ;-P

> Umm, perhaps F->funcname(@args), or PG->funcname(@args), or ... ?
>
> Anyone got any better suggestions?

PG is good. Or maybe DB?

>> By the way, I think it needs some documentation explaining how to load it inside PL/Perl.
>
> I thought about that, and started to write it, but dropped it for now.
> I'll wait till my "cunning plan" to share code with the Safe compartment
> (aka PostgreSQL::PLPerl::Injector) is done then document how call() can
> be used in both plperlu and plperl.

Ah, okay.

Best,

David

On 17/02/10 18:30, David E. Wheeler wrote:
> On Feb 17, 2010, at 4:28 AM, Tim Bunce wrote:
>
>> Umm, perhaps F->funcname(@args), or PG->funcname(@args), or ... ?
>>
>> Anyone got any better suggestions?
>
> PG is good. Or maybe DB?

It's a module whose only use is embedded in a DB called PG - not sure
those carry any extra info. It also treads on the toes of
"PG->not_a_function" should such a beast be needed.

I like "F->funcname" or "FN->funcname" myself.

--
Richard Huxton
Archonet Ltd

On Wed, Feb 17, 2010 at 10:30:03AM -0800, David E. Wheeler wrote:
> On Feb 17, 2010, at 4:28 AM, Tim Bunce wrote:
>
> >> Yes, but if it's a variadic function, I suspect that it won't often be
> >> called with the same number of args. So you'd potentially end up
> >> caching a lot of extra stuff that would never be used again.
> >
> > Potentially. Patches welcome!
>
> GitHub. ;-P

http://github.com/timbunce/posgtresql-plperl-call

> > Umm, perhaps F->funcname(@args), or PG->funcname(@args), or ... ?
> >
> > Anyone got any better suggestions?
>
> PG is good. Or maybe DB?

On Thu, Feb 18, 2010 at 08:26:51AM +0000, Richard Huxton wrote:
>
> It's a module whose only use is embedded in a DB called PG - not
> sure those carry any extra info. It also treads on the toes of
> "PG->not_a_function" should such a beast be needed.
>
> I like "F->funcname" or "FN->funcname" myself.

Thanks. I quite like FN.

Anybody else want to express an opinion on the color if this bikeshed
before I repaint it?

Tim.

Tim Bunce <Tim(dot)Bunce(at)pobox(dot)com> writes:
>> I like "F->funcname" or "FN->funcname" myself.
>
> Thanks. I quite like FN.
>
> Anybody else want to express an opinion on the color if this bikeshed
> before I repaint it?

I wouldn't have, but since you ask... What about reusing the internal
name, you seem to be emulating the fmgr in plperl. My proposal is thus
FMGR.

Regards,
--
dim

On Feb 18, 2010, at 1:35 AM, Tim Bunce wrote:

> http://github.com/timbunce/posgtresql-plperl-call

Thanks, forked.

> Thanks. I quite like FN.
>
> Anybody else want to express an opinion on the color if this bikeshed
> before I repaint it?

I like PG because it lets you know that you're calling a function that's specific to the database. FN could mean anything.

> I wouldn't have, but since you ask... What about reusing the internal
> name, you seem to be emulating the fmgr in plperl. My proposal is thus
> FMGR.

Meh. Too long.

David