Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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. –  Erwin Brandstetter Jun 24 '13 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 Jun 25 '13 at 11:03
add comment

1 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.

share|improve this answer
    
Is it as good as non-postgresql solutions like solr? –  HappyCoder Oct 12 '13 at 13:27
    
Depending on your definition of "good". Although it's not correct comparing a platform with with a single query anyway. –  Stoleg Oct 13 '13 at 16:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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