PostgreSQL reverse pattern matching (pattern in field)

Question

I have a table like that:

id: 1
pattern: /test.*

id: 2
pattern: /hello-world

and this input value: /test-request

Now i want to create a query for the given input and postgres should return the row, where the pattern from the second column matches the input (a regex search but the regex is in the field to search)

Is that possible with a postgres database?

Answer 1

You can use a column value for the regex operator:

select id, pattern 
from patterns
where '/test-request' ~* pattern;

~* does a case-insensitive match, ~ does a case sensitive match.

SQLFiddle example: http://sqlfiddle.com/#!15/388a4/2