postgresql multiple column search with ranking -

i want search multiple columns in multiple tables. this: given tables:

users id first_name last_name email

companies user_id address

lands name company_id

lets user johny bravo(johny.bravo@gmail.com) working in washington in united states. want find record based on query "ate" -> united states, or "rav" bravo

when type "rav" johny bravo rank higher johny bravos other emails first in results

how can implement such functionality? i've looked @ ts_vector , ts_rank seems supports right wildcard ("to_tsquery('brav:*')") work, don't need full-text-search functionalities(i adresses , usernames no need alias names etc.) can wildcard search have manually calculate ranking in application

you use pg_trgm extension.

you must have contrib installed, install extension:

create extension pg_trgm; 

then can create trigram indexes:

create index user_idx on user using gist (user_data gist_trgm_ops); 

and can query give first 10 similar values:

select * user order user_data <-> 'rav' limit 10; 

note can replace user_data immutable function, can concatenate of info 1 (text) field enabling search across more fields.

to "ranking score", can use similarity function, returns 1 identical strings , 0 unrelated.

if need full text search across whole database, better solution might separate search facility, such apache solr.