Re: Use UNKNOWN with PL/Perl spi_prepare()?

On Apr 6, 2010, at 4:54 PM, Tom Lane wrote:

>> try=# create or replace function try() returns void language plperl as $$
>> spi_prepare('select abs($1)', 'text');
>> $$;
>> CREATE FUNCTION
>> try=# select try();
>> ERROR: error from Perl function "try": function abs(text) does not exist at line 2.
>
> Well, yes; what's your point? How would you actually *use* this if you
> had it? In particular what do you see yourself passing to the eventual
> exec call?

Yes, I would use unknown, because as you said, in Perl the types of values are unknown.

DBD::Pg makes extensive use of unknown for prepares. If I do

my $sth = $dbh->prepare('SELECT foo FROM bar WHERE baz = ?');

DBD::Pg effectively sends:

PREPARE dbdpg_1(unknown) AS SELECT from FROM bar WHERE baz = ?';

I'd love to be able to do the same from PL/Perl.

Specifically, I'm writing a utility function that will be used by other PL/Perl code, and that function doesn't know what will be passed to it. It looks like this:

$_SHARED{select_row} = sub {
my $query = shift;
if (@_) {
my $plan = spi_prepare($query, ('unknown') x @_ );
return spi_exec_prepared($plan, @_)->{rows}[0];
} else {
return spi_exec_query($query, 1)->{rows}[0];
}
};

It might be called without params:

my $time = $_SHARED{select_row}->('SELECT now()')->{now};

Or with text params:

my $len = $_SHARED{select_row}->(
'SELECT length($1)', 'foo'
)->{length};

Or with any other type of params:

my $abs = $_SHARED{select_row}->(
'SELECT abs($1)', -42
)->{abs};

It needs not to care.

Best,

David