[Dbix-class] Query using function() on column name

[Bill Moseley]

I have created a partial index in Postgresql using lower() on a column name
so I need to use that in my query to make use of the index.

The Cookbook<http://search.cpan.org/~ribasushi/DBIx-Class-0.08250/lib/DBIx/=
Class/Manual/Cookbook.pod#Using_SQL_functions_on_the_left_hand_side_of_a_co=
mparison>discusses
this usage.    But, I also need to identify the bind parameter.

Is this the correct syntax for the bind value?

my $rs =3D $schema->resultset( 'User' )->search(
    \['lower( first_name ) like ?', [{ dbic_colname =3D> 'first_name' },
"$query%"]],
);


Second, what is the correct way to escape $query?

I don't want percents or underscores in $query to represent pattens.   Does
DBIC or SQL::Abstract provide a helper for this?

What I've done in the past was strip all backslahses and then do this per
DBI docs:

         $esc =3D $dbh->get_info( 14 );  # SQL_SEARCH_PATTERN_ESCAPE
         $search_pattern =3D~ s/([_%])/$esc$1/g;


Thanks,

-- =

Bill Moseley
moseley at hank.org
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20130527/cd2=
cd3be/attachment.htm