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