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

CodePudding user response：

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.

CodePudding user response：

I think you mix a lot of things in your question. You make it sound like it is an SQL::Abstract issue, when your real issue is with the underlying SQL itself.

First of all, I would personally avoid using SQL::Abstract in most cases (it is sometimes VERY slow to create complex queries and you can't be sure of its output) and definitely in cases like this where you want something non-standard.

So, moving on, I am not sure what you mean by IP type, from your postgres tag and the mention of CIDR I suspect you mean the inet type? If so, the equivalent of LIKE is to use subnet masks, which is basically the whole reason to use inet instead of a text/varchar field. For 192.168.* for example you would do something like below using the subnet slash notation:

SELECT * FROM users WHERE ip << inet '192.168.0.0/16'

If you don't want to treat IPs as actual IPs and take advantage of things like above, but instead you want to treat them like text (e.g. you want to search 192.16% with that producing 192.168.* results but along with 192.16.* etc which is not done with subnet masks), then you either use a text type in the first place, or as you said convert on the fly to use LIKE directly:

SELECT * FROM users WHERE TEXT(ip) LIKE '192.168.%'

There is a performance penalty over using the subnet masks, but whether that may be an issue depends on your data of course.

Note cidr works similar to inet, it won't help you with a LIKE.