Multiple words search across multiple columns

Question

I have a simple table:

CREATE TABLE contact (
    id serial NOT NULL,
    "firstName" varchar NOT NULL,
    "middleName" varchar NULL,
    "lastName" varchar NOT NULL
);

How to construct a query to search across all fields with partial support?

Assuming I have the following entries:

firstName middleName lastName
John      Mark       Smith
Barbara   Alice      Johnson
John      Bob        Johson

When I search for:

'joh smi' or 'smi joh' I would like to retrieve the first record

but when I search for 'joh joh' I would only like to retrieve the last record but not the first two.

Answer 1

If you use two words to search (one name and one last name but you don't know which is which), you can create a function like this:

CREATE FUNCTION twoWordsSearchForContacts(words text) RETURNS SETOF contact AS
$$

DECLARE

aWords text[] := string_to_array(words, ' ');

word1 text := aWords[1];
word2 text := aWords[2];

BEGIN

  RETURN QUERY SELECT * 
               FROM contact 
               WHERE ("lastName" ILIKE '%' || word1 || '%' AND "firstName" || ' ' || "middleName" ILIKE '%' || word2 || '%')
                       OR ("lastName" ILIKE '%' || word2 || '%' AND "firstName" || ' ' || "middleName" ILIKE '%' || word1 || '%');
END;

$$ LANGUAGE plpgsql;

Using the function:

SELECT * FROM twoWordsSearchForContacts('smi joh');

You can also split words directly in the query without using a function.