
SELECT to_tsvector(l_name)
FROM   lehrender
;

SELECT to_tsvector('german', l_name)
FROM   lehrender
;

SELECT to_tsvector('german', 
                   coalesce(l_titel, '') || ' ' ||
                   coalesce(l_name, '')  || ' ' ||
                   coalesce(l_email, '')
                  )
FROM   lehrender
;


CREATE INDEX index_volltext_lehrender
ON lehrender
USING gin(to_tsvector('german', 
                      coalesce(l_titel, '') || ' ' ||
                      coalesce(l_name, '')  || ' ' || 
                      coalesce(l_email, '')
                     )
         );


SELECT *
FROM   lehrender
WHERE  to_tsvector('german',
                      coalesce(l_titel, '') || ' ' ||
                      coalesce(l_name, '')  || ' ' ||
                      coalesce(l_email, '')
                     )
       @@

       to_tsquery('german', 'dr') 
;

SELECT *
FROM   lehrender
WHERE  to_tsvector('german',           
                      coalesce(l_titel, '') || ' ' ||
                      coalesce(l_name, '')  || ' ' ||
                      coalesce(l_email, '')
                     )
       @@
 
       to_tsquery('german', 'dr & prof')


ALTER TABLE lehrender ADD COLUMN volltext tsvector;
UPDATE lehrender 
SET volltext =
    to_tsvector('german',           
                      coalesce(l_titel, '') || ' ' ||
                      coalesce(l_name, '')  || ' ' ||
                      coalesce(l_email, '')
                     )
    ;

CREATE INDEX index_volltext_lehrender_2
ON lehrender
USING gin(volltext);

SELECT *
FROM   lehrender
WHERE  volltext @@ to_tsquery('german', 'dr & prof')
;

SELECT *
FROM   lehrender
WHERE  volltext @@ to_tsquery('german', 'dr | john')
;

SELECT *
FROM   lehrender
WHERE  volltext @@ to_tsquery('german', 'dr & !wolfgang')
;


