Re: DBD::PgSPI 0.02

Michael Fuhr wrote:

>>DBI? yes, $pg_dbh->quote('foo')
>>    
>>
>
>Yeah, I know about DBI, but since we currently can't use it in
>trusted code I was wondering what we *could* use.  With DBI I'd be
>using placeholders wherever possible, but unless I've missed something
>spi_exec_query() requires values to be interpolated into the query
>string.  Danger, danger.
>  
>


One of the relatively unnoticed features of 8.0's plperl is %_SHARED. 
This is a hash available to all trusted and untrusted code, and can be 
used to store arbitrary objects. That includes references to 
subroutines. So you could have an init function that you call once per 
session that sets up some utility functions for you and stores them 
there. Writing a quote function shuld not be too hard. (Some 
automatically called init code is another item on the plperl agenda.)

moderately tested example:
-- set up the quote function
CREATE OR REPLACE FUNCTION myfuncs() RETURNS void LANGUAGE plperl AS $$
   $_SHARED{myquote} = sub 
   {
      my $arg = shift;
       $arg =~ s/(['\\])/\\$1/g;
       return "'$arg'";
   };

$$;

SELECT myfuncs();
-- set up a function that uses the quote function
CREATE OR REPLACE FUNCTION use_quote(text) RETURNS text LANGUAGE plperl AS $$
my $text_to_quote = shift;my $qfunc = $_SHARED{myquote};return &$qfunc($text_to_quote);

$$;

SELECT use_quote($$bl\ur'fl$$);



cheers

andrew