SQL::Abstract Type Cast Column

Question

Using SQL::Abstract I need to type cast an IP column to TEXT in order to be able to search using LIKE.

I only found a "hacky" way to achieve it with:

where( { ip => { '::TEXT LIKE' => $myParameter } } )

Which generates

WHERE ( "ip" ::TEXT LIKE ? )

Question is: Is there a less hacky or official way to achieve this?

Questions are not:

• Will the performance be poor?
• Should I use a TEXT column instead of an IP column?
• Are searches using CIDR a better alternative

Answer 1

The issue was in Mojo::Pg which adds a quote_char of " to the SQL::Abstract object.

When I set this to the empty string, this will work as expected:

where( { 'ip::TEXT' => { 'LIKE' => $myParameter } } )

But, to be complete, I had to use

where( { 'host(ip)' => { 'LIKE' => $myParameter } } )

because ::TEXT will give the IP with an appended /32.