1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
| WITH x AS (
SELECT unnest(string_to_array(trim(strip(to_tsvector('english','my super text1 is not like text2 but word is not exact'))::text,''''),''' ''')) AS val
), y AS (
SELECT unnest('{text,word,miss}'::text[]) AS term
), tbl AS (
SELECT term,min(val<->term)
FROM x,y
WHERE val%term AND term IN ( SELECT unnest('{text,word,miss}'::text[]) )
GROUP BY term
)
SELECT *,
(count(1) OVER ())>=(WITH c AS (
SELECT unnest('{text,word,miss}'::text[])
) SELECT count(1) FROM c
)-1 AS result
FROM tbl
; |
Partager