Match string pattern to any array element

I'm trying to match a string pattern to any string in an array:

SELECT 'abc' LIKE ANY('{"abc","def"}') -- TRUE

-- BUT

SELECT 'ab%' LIKE ANY('{"abc","def"}') -- FALSE, I expect TRUE

What is wrong with the second query?

It would be nice if PostgreSQL allowed ANY on the left hand operand, but sadly it doesn't. So you need the commutator of LIKE. PostgreSQL doesn't come with one, but you can create your own.

You need a function that reverses the order of arguments to LIKE, and then use that to create an operator that can be used in conjunction with ANY. User created operators have to be named with symbols, not text, so I'm picking '<~~'. '~~' is the built-in synonym for LIKE, so I'm adding '<' as a pneumonic to make it "go the other way". You can pick any unused name you want, though.

create function reverse_like (text, text) returns boolean language sql as $ select $2 like $1 $;
create operator <~~ ( function =reverse_like, leftarg = text, rightarg=text );
SELECT 'ab%' <~~ ANY('{"abc","def"}');

You should probably tag the function as immutable, and if you are using v9.6 or above, also as parallel safe.

create or replace function reverse_like (text, text) returns boolean language sql as
 $ select $2 like $1 $ immutable parallel safe;

StackExchange Database Administrators Q#228235, answer score: 7