Postgresql – Full text search on multiple tables in PostgreSQL

full-text-searchpostgresqlpostgresql-9.1

I have several tables that have related data. I want to have a search input box on the dashboard of the system that will search for anything (client, invoice, delivery note, …).

Is it possible to have a FTS implementation in PostgreSQL that will get the item searched for in more than one table and get the results?

For example, if the user typed 45, the query should look in the clients table and return the clients with id 45 or name has 45, also look in the invoices table and return the invoices with number 45, and also look into the delivery notes table and return the delivery notes with number 45.

Or is there a better way of solving this problem without using full text search?

Best Answer

General idea of Full-Text Search is to provide more functionality for naitive language searches. it allows using stop words (words, that do not contribute to subject of the search), search for synonims, specify distance between words etc.

To accomplish, what you need a procedure with several unions is enough. Design layout of the output first. Than do a query for each table you need to search in. If you need to show value found, and number af columns varies too much, then have several queries to same table returining different column of interes.

If column data types cannot be converted implicitly, then explicitly convert them to text in SELECT and WHERE. 

CREATE FUNCTION find_everything(searchTerm TEXT)
RETURNS RETURNS TABLE(
   ID integer, 
   Description text,
   Source_table text) AS
$$ 

Declare
    searchIn text;
BEGIN
    searchIn = '%' || searchTerm || '%';

    For results in 
      SELECT 
        Client_Id as ID, 
        Name as Description,
        'Clients Table' as Source_Table
      FROM Clients 
      WHERE Client_Id ILIKE searchIn OR Name ILIKE searchIn

      UNION ALL

      SELECT 
        Invoice_Id as ID, 
        Invoice_Description as Description,
        'Invoices Table' as Source_Table
      FROM Invioces 
      WHERE  Invoice_Id ILIKE searchIn OR Invoice_Description ILIKE searchIn

      UNION ALL
      ...

      return next results;
    end loop;
END;
$$ language plpgsql;

P.S. Sorry if there are errors in code. It is to show the idea.

Related Question