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 :
SELECT
user_nom,
user_prenom,
cp,
id
FROM
ii_custom.sncf_users u
WHERE
-- no search, looking for the whole table
(:C_SNCF_FREE_SEARCH::TEXT IS NULL)
OR
-- 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 ‘1.2.3.4’ 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 :
and
(
(:C_SNCF_FREE_SEARCH::text is null) or
( basequery.id in
(
select a.id from ii_custom.sncf_responsabilite_hierarchique a
left join ii_custom.sncf_perimetres p ON p.id = a.fk_perimetre_id
left join ii_custom.sncf_users u1 ON u1.id = a.fk_user_respo
left join ii_custom.sncf_users u2 ON u2.id = a.fk_user_validator
where a.id = basequery.id
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))
)
)