Search filter (google like)

In order to create a filter field, allowing you to enter multiple words to do a free search (like google), you have to create a parameter string, and add it on your dashboard as simple input filtre.

For my example its name is C_SNCF_FREE_SEARCH

here is the query looking for all my users :

	ii_custom.sncf_users u
-- no search, looking for the whole table
-- specific search in all field of my table u 
  (	SELECT array_dims(regexp_matches(lower(ROW(u.*)::TEXT), '^(?=.*' || lower(REPLACE (:C_SNCF_FREE_SEARCH::TEXT, ' ', ')(?=.*'))|| ').*$')) IS NOT NULL)

the clause where will act like :
→ if search is NULL, nothing happens
→ if search isnt null, it will take the lines that match the words written, multiple words allowed (separed by space, clause AND for all word)

In my picture example, it wil take the line that contains ‘blinou’ AND ‘’ in any field.

Here, the search can be applied on every field of my table user > ROW(u.*)::TEXT
It means the search could be on an hidden field, like the mail or the ldap profil

you could be precising the list of field you want to do in ROW:

( SELECT array_dims(regexp_matches(lower(ROW(u.user_nom, u.user_prenom, u.cp)::TEXT), ‘^(?=.’ || lower(REPLACE (:C_SNCF_FREE_SEARCH::TEXT, ’ ', ')(?=.’))|| ‘).*$’)) IS NOT NULL)

If you are looking for a table form that use select on foreign table, to compute status/company/group, i recommand using it this way :

(:C_SNCF_FREE_SEARCH::text is null) or 
( in 
  select from ii_custom.sncf_responsabilite_hierarchique a
   left join ii_custom.sncf_perimetres p ON = a.fk_perimetre_id 
   left join ii_custom.sncf_users u1 ON = a.fk_user_respo
   left join ii_custom.sncf_users u2 ON = a.fk_user_validator 
   where =
   and (select  array_dims(regexp_matches(lower(row(a.*)::TEXT||row(p.*)::TEXT||row(u1.*)::TEXT||row(u2.*)::TEXT), '^(?=.*'||lower(replace (:C_SNCF_FREE_SEARCH::text,' ',')(?=.*'))||').*$')) is not null))