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
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
