Handling LIKE queries

LIKE is a widely used component of the SQL language that allows the user to perform fuzzy searches. Sometimes, you have an idea about what you are looking for but you are not exactly sure what it is. In these situations, a wildcard search can come in handy and improve user experience dramatically. Just think of the following example: you are reading a fax that has just dropped in. On a poor-quality fax, can you really distinguish a B from an 8 all the time? I guess not. LIKE will fix that by allowing you to put placeholders in a query.

To demonstrate the power of LIKE and the problems arising along with it, I have compiled a small example. The goal is to look for names of cities and villages. Here is the required data:

test=# CREATE TABLE t_location (name text);
CREATE TABLE
test=# COPY t_location FROM PROGRAM 
    'curl www.cybertec.at/secret/orte.txt';
COPY 2354

The CREATE TABLE part is pretty straightforward and easy to understand. To feed data to the system...