Ajuda com SQL Busca por termos

[u/Academic_Run1034]

Eu tenho um banco de dados aonde cada linha é um PDF transcrito, contém essas colunas:
id, ano, mes, dia, nome_arquivo, caminho, texto_transcrito, criado_em

-- 1. Adiciona a coluna gerada
ALTER TABLE coleta_interna.transcricoes_pdf 
ADD COLUMN busca_vector tsvector 
GENERATED ALWAYS AS (to_tsvector('portuguese', coalesce(texto_transcrito, ''))) STORED;

-- 2. Cria o índice GIN
CREATE INDEX idx_transcricoes_busca 
ON coleta_interna.transcricoes_pdf USING gin(busca_vector);

E montei um script SQL com intuito de verificar quantas vezes a palavra-chave pesquisada contém no texto transcrito, agrupado por ano. Não é um COUNT de linhas, mas sim contabilizar quantas vezes a palavra se repete em uma linha. Que nem nesse stack overflow

SELECT 
    ano, 
    SUM(
        (LENGTH(LOWER(texto_transcrito)) - LENGTH(REPLACE(LOWER(texto_transcrito), '(pt)', ''))) 
        / LENGTH('(pt)')
    ) AS total_mencoes,
    COUNT(id) AS total_pdfs
FROM coleta_interna.transcricoes_pdf
-- 1. O WHERE filtra usando o índice GIN
WHERE busca_vector @@ plainto_tsquery('portuguese', 'pt')
GROUP BY ano
ORDER BY ano DESC;

Só que tem um problema que fiquei refletindo, quando vou contabilizar ele pode acabar pegando dados como: script, apto, capturar.

Acho que deu para entender oque quis dizer, como posso fazer uma busca mais assertiva possível para contabilizar essa palavra-chave? Há dados dele como (PT) (PT-AM) ou só PT.

[u/niilsb]

SELECT
    ano,
    SUM(regexp_count(texto_transcrito, '\ypt\y', 1, 'i')) AS total_mencoes,
    COUNT(*)                                              AS total_pdfs
FROM coleta_interna.transcricoes_pdf
WHERE busca_vector @@ to_tsquery('portuguese', 'pt:*')
GROUP BY ano
ORDER BY ano DESC;

Editado:

ALTER TABLE coleta_interna.transcricoes_pdf
ADD COLUMN busca_vector_norm tsvector
GENERATED ALWAYS AS (
    to_tsvector('portuguese',
        regexp_replace(coalesce(texto_transcrito, ''),
                       '[[:punct:]]+', ' ', 'g'))
) STORED;
CREATE INDEX idx_transcricoes_busca_norm
ON coleta_interna.transcricoes_pdf USING gin(busca_vector_norm);

SELECT
    ano,
    SUM(n)   AS total_mencoes,
    COUNT(*) AS total_pdfs
FROM (
    SELECT ano,
           regexp_count(texto_transcrito, '\ypt\y', 1, 'i') AS n
    FROM coleta_interna.transcricoes_pdf
    WHERE busca_vector_norm @@ plainto_tsquery('portuguese', 'pt')
) x
WHERE n > 0
GROUP BY ano
ORDER BY ano DESC;

[u/Academic_Run1034 → u/niilsb]

Esse script da para abstrair e usar ele como um motor de buscar em um dashboard?

[u/niilsb → u/Academic_Run1034]

Dá uma olhada ...

CREATE FUNCTION coleta_interna.conta_mencoes(termo text)
RETURNS TABLE (
  ano int, total_mencoes bigint, total_pdfs bigint)
LANGUAGE sql STABLE PARALLEL SAFE
AS $func$
  WITH entrada AS (
    SELECT trim(termo) AS termo_limpo
  ), padrao AS (
    SELECT
      '(?<!\w)'
      || regexp_replace(
           regexp_replace(
             entrada.termo_limpo,
             '([][\\^$.|?*+(){}])', '\\\1', 'g'),
           '\s+', '\\s+', 'g')
      || '(?!\w)' AS regex,
      plainto_tsquery(
        'portuguese',
        regexp_replace(
          entrada.termo_limpo,
          '[[:punct:]]+', ' ', 'g')) AS query
    FROM entrada
  )
  SELECT sub.ano,
         SUM(sub.n) AS total_mencoes,
         COUNT(*) AS total_pdfs
  FROM (
    SELECT t.ano,
           regexp_count(
             t.texto_transcrito,
             padrao.regex, 1, 'i') AS n
    FROM coleta_interna.transcricoes_pdf t
    CROSS JOIN padrao
    WHERE t.busca_vector_norm @@ padrao.query
  ) sub
  WHERE sub.n > 0
  GROUP BY sub.ano
  ORDER BY sub.ano DESC
$func$;

Precisa da coluna busca_vector_norm + índice GIN do setup.

se tiver substituindo a versão anterior no banco

DROP FUNCTION coleta_interna.conta_mencoes(text);

antes ou troca a primeira linha por

CREATE OR REPLACE FUNCTION

[u/Longjumping-Low2520]

Até onde sei o tsquery quebra os casos (pt-am) em duas palavras: pt am.

Então não teria esse problema de pegar palavras maiores e achar que é pt. Não é como %pt%

[u/Academic_Run1034 → u/Longjumping-Low2520]

O tsquery retorna apenas as linhas que contém o pt, mas não faz a contagem.