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?

  • 2
    You can easily write a function returning records from multiple tables. But you'd have to define what you want to find exactly. 45, 123456, 45fingers, 1945, killed 45 people. If it's not just digits: also case insensitive? Even fuzzy? Then you need to define the return type and the sort order exactly. Commented Jun 24, 2013 at 20:06
  • yes i would like the function to give the results just like u have listed and also include the source(if its from invoice,names,...) Could you have like a sample function?
    – indago
    Commented Jun 25, 2013 at 11:03

1 Answer 1


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)
   ID integer, 
   Description text,
   Source_table text) AS

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

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


        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


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

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

  • Is it as good as non-postgresql solutions like solr? Commented Oct 12, 2013 at 13:27
  • Depending on your definition of "good". Although it's not correct comparing a platform with with a single query anyway.
    – Stoleg
    Commented Oct 13, 2013 at 16:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.