[firebase-br] Índice de expressão e stored procedure

[Daniel / Tecnobyte]

Tenho a seguinte tabela:


CREATE TABLE Pessoa(
  ...
  Identidade       VARCHAR(20) COLLATE PT_BR,
  ...);


Neste campo "Identidade" o usuário poderá um número de identidade com ou sem formatação, incluindo o órgão expedidor quando for o caso.

Exemplos:

001.002.003 SSP/RO
001002003 SSP/RO
001002003 SSP-RO

Mas neste caso desejo criar um índice permita pesquisar rapidamente sem informar a formatação e o sistema deverá encontrar em qualquer dos formatos acima. Além disso quero que estes exemplos acima sejam considerados iguais e não seja permitido duplicar.

Para começar fiz alguns testes com índice baseado em expressão e com função interna funcionou perfeito. Exemplo:

CREATE UNIQUE ASC INDEX Idx_Pessoa_Identidade2 ON Pessoa
  COMPUTED BY(UPPER(Identidade));

SELECT * FROM Pessoa
WHERE UPPER(Identidade) = '1'

PLAN (PESSOA INDEX (IDX_PESSOA_IDENTIDADE2))

Então pensei: crio uma stored procedure que retornará apenas os dígitos e letras contidos no número do documento e indexo com base neste procedimento, como segue:

SET TERM ^ ;

CREATE OR ALTER PROCEDURE Proc_NumDoc(NumDoc VARCHAR(20))
RETURNS(Retorno VARCHAR(20)) AS
DECLARE VARIABLE Ch CHAR(1);
BEGIN
  Retorno = '';
  WHILE (NumDoc IS NOT NULL AND NumDoc <> '') DO
  BEGIN
    Ch = UPPER(SUBSTRING(NumDoc FROM 1 FOR 1));
    IF ((Ch BETWEEN '0' AND '9') OR (Ch BETWEEN 'A' AND 'Z')) THEN
      Retorno = Retorno || Ch;
    NumDoc = SUBSTRING(NumDoc FROM 2 FOR 20);
  END
  IF (Retorno = '') THEN
    Retorno = NULL;
  SUSPEND;
END^

SET TERM ; ^

CREATE UNIQUE ASC INDEX Idx_Pessoa_Identidade ON Pessoa
  COMPUTED BY((SELECT Retorno FROM Proc_NumDoc(Identidade)));

Isto resolveu o problema da duplicação, embora a mensagem tenha ficado esquisita. Veja:

---------------------------
attempt to store duplicate value (visible to active transactions) in unique index "@1"
---------------------------

O maior problema é que o índice deveria ter sido usado no SELECT abaixo, mas não foi. Veja:

SELECT * FROM Pessoa
WHERE ((SELECT Retorno FROM Proc_NumDoc(Identidade))) = '1'

PLAN (PROC_NUMDOC NATURAL)
PLAN (PESSOA NATURAL)

---

Sei que posso resolver o problema criando um campo extra onde eu deveria armazenar o número do documento sem a formatação e fazer toda a indexação e pesquisa sobre este campo. Este poderia ser alimentado via trigger, mas é uma gambiarra terrível. rsrs

Outra opção seria gravar tudo sem formatação, uma vez que não dá para forçar uma formatação padrão devido a natureza do dado (como trata-se de identidade, RG, para cada UF pode haver um formato diferente). 

Enfim, qualquer das opções não ficará perfeita. O ideal seria mesmo indexar desprezando-se a formatação. 

Alguém tem alguma sugestão melhor para resolver o problema?

NOTA! Ainda não testei índice baseado em expressão com chamada a uma UDF.

Atenciosamente.

Daniel P. Guimarães
Tecnobyte Informática
www.tecnobyte.com.br