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