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

[Peter Rabbitson]

On Mon, May 27, 2013 at 04:15:08PM -0700, Bill Moseley wrote:
> 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_comparison>discusses
> this usage.    But, I also need to identify the bind parameter.
> 
> Is this the correct syntax for the bind value?
> 
> my $rs = $schema->resultset( 'User' )->search(
>     \['lower( first_name ) like ?', [{ dbic_colname => 'first_name' },
> "$query%"]],
> );

No it isn't. You have an extra set of []s in there. The "litral with 
bind" syntax is defined thus:

\[ $sql_portion, $bind1, $bind2, $bind3,... ]

Each $bindX element in turn can be in any format as defined at the end 
of this node (new formats are available since 0.08250): 
http://search.cpan.org/~ribasushi/DBIx-Class-0.08250/lib/DBIx/Class/ResultSet.pm#DBIC_BIND_VALUES

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

There currently is no canned solution for this. The feature is blocked 
due to the unavailability of a way to mark some nodes as a specific 
dialect of SQL (per engine). In other words this is something which will 
take place post-Data::Query.

Cheers