31.12. Operadores definidos pelo usuário

Exemplo 31-10. Operadores para comparar texto sem diferenciar letras minúsculas, maiúsculas ou acentuadas

Os operadores binários definidos pelo usuário mostrados neste exemplo (<<<, >>>, !== e ===) têm por finalidade comparar dois textos sem diferenciar letras minúsculas, maiúsculas ou acentuadas, e também desprezando os espaços em branco no início e no fim dos textos. Além de serem utilizados para comparar dois textos diretamente, estes operadores também são utilizados em consultas para classificar a saída em ordem ascendente e descendente através da cláusula ORDER BY expressão USING operador do comando SELECT.

Abaixo está mostrado o arquivo operador.sql utilizado para criar e testar os operadores:

/*
 * Retorna verdade se os dois argumentos forem considerados iguais,
 * falso caso contrário.
 */
CREATE FUNCTION fun_igual_ci_ai(a text, b text) RETURNS boolean AS $$
BEGIN
   RETURN (lower(to_ascii(trim(a))) = lower(to_ascii(trim(b))));
END;
$$ LANGUAGE plpgsql STRICT;
/*
 * Retorna verdade se os dois argumentos forem considerados diferentes,
 * falso caso contrário.
 */
CREATE FUNCTION fun_difer_ci_ai(a text, b text) RETURNS boolean AS $$
BEGIN
   RETURN (lower(to_ascii(trim(a))) != lower(to_ascii(trim(b))));
END;
$$ LANGUAGE plpgsql STRICT;
/*
 * Retorna verdade se o primeiro argumento for considerado maior que o
 * segundo argumento, falso caso contrário.
 */
CREATE FUNCTION fun_maior_ci_ai(a text, b text) RETURNS boolean AS $$
BEGIN
   RETURN (lower(to_ascii(trim(a))) > lower(to_ascii(trim(b))));
END;
$$ LANGUAGE plpgsql STRICT;
/*
 * Retorna verdade se o primeiro argumento for considerado menor que o
 * segundo argumento, falso caso contrário.
 */
CREATE FUNCTION fun_menor_ci_ai(a text, b text) RETURNS boolean AS $$
BEGIN
   RETURN (lower(to_ascii(trim(a))) < lower(to_ascii(trim(b))));
END;
$$ LANGUAGE plpgsql STRICT;
/*
 * Criação dos operadores que implementam as funções de comparação.
 */
CREATE OPERATOR <<< (
    PROCEDURE = fun_menor_ci_ai,
    LEFTARG   = text,
    RIGHTARG  = text
);
CREATE OPERATOR >>> (
    PROCEDURE = fun_maior_ci_ai,
    LEFTARG   = text,
    RIGHTARG  = text
);
CREATE OPERATOR !== (
    PROCEDURE = fun_difer_ci_ai,
    LEFTARG   = text,
    RIGHTARG  = text
);
CREATE OPERATOR === (
    PROCEDURE = fun_igual_ci_ai,
    LEFTARG   = text,
    RIGHTARG  = text
);
\pset border 2
\pset null '(nulo)'
\pset title 'Resultados das expressões'
SELECT
    ''' São Bento  '' = ''SAO BENTO''' AS expressão,
    ' São Bento  ' = 'SAO BENTO' AS resultado
UNION
SELECT
    ''' São Bento  '' === ''SAO BENTO''',
    ' São Bento  ' === 'SAO BENTO'
UNION
SELECT
    ''' São Bento  '' === NULL',
    ' São Bento  ' === NULL
UNION
SELECT
    ''' São Bento  '' === ''  SAO BENEDITO''',
    ' São Bento  ' === '  SAO BENEDITO'
UNION
SELECT
    ''' São Bento  '' !== ''SAO BENTO''',
    ' São Bento  ' !== 'SAO BENTO'
UNION
SELECT
    ''' São Bento  '' >>> ''  SAO BENEDITO''',
    ' São Bento  ' >>> '  SAO BENEDITO'
UNION
SELECT
    ''' São Bento  '' <<< ''  SAO BENEDITO''',
    ' São Bento  ' <<< '  SAO BENEDITO';
\pset title 'Ascendente'
SELECT nome FROM tbl_santos ORDER BY nome USING <<<;
\pset title 'Descendente'
SELECT nome FROM tbl_santos ORDER BY nome USING >>>;

A seguir está mostrado o resultado do processamento do arquivo:

# psql -U teste -f operador.sql -o operador.out -q teste
# cat operador.out
             Resultados das expressões
+-------------------------------------+-----------+
|              expressão              | resultado |
+-------------------------------------+-----------+
| ' São Bento  ' !== 'SAO BENTO'      | f         |
| ' São Bento  ' <<< '  SAO BENEDITO' | f         |
| ' São Bento  ' = 'SAO BENTO'        | f         |
| ' São Bento  ' === '  SAO BENEDITO' | f         |
| ' São Bento  ' === 'SAO BENTO'      | t         |
| ' São Bento  ' === NULL             | (nulo)    |
| ' São Bento  ' >>> '  SAO BENEDITO' | t         |
+-------------------------------------+-----------+
(7 linhas)

   Ascendente
+--------------+
|     nome     |
+--------------+
| Santa Clara  |
| SAO BENEDITO |
|  São Bento   |
| SAO BENTO    |
| São José     |
| (nulo)       |
+--------------+
(6 linhas)

  Descendente
+--------------+
|     nome     |
+--------------+
| São José     |
| SAO BENTO    |
|  São Bento   |
| SAO BENEDITO |
| Santa Clara  |
| (nulo)       |
+--------------+
(6 linhas)
  

Exemplo 31-11. Operador para concatenar texto sem nunca retornar nulo

O operador binário definido pelo usuário mostrado neste exemplo (||+) têm por finalidade concatenar dois textos sem nunca retornar o valor nulo. Este exemplo foi baseado no artigo How to add custom operators in PostgreSQL, escrito por Josh Berkus.

Abaixo está mostrado o arquivo concatena.sql utilizado para criar e testar o operador:

/*
 * Concatena duas cadeias de caracteres sem nunca retornar nulo.
 */
CREATE FUNCTION fun_concatena_nunca_nulo(text, text) RETURNS text AS $$
    SELECT COALESCE($1,'') || COALESCE($2,'');
$$ LANGUAGE SQL IMMUTABLE;

CREATE OPERATOR ||+ (
    PROCEDURE = fun_concatena_nunca_nulo,
    LEFTARG   = text,
    RIGHTARG  = text
);
\pset border 2
\pset null '(nulo)'
\pset title 'Resultados das expressões'
SELECT
    '''Postgre'' || NULL' AS expressão,
    'Postgre' || NULL AS resultado
UNION
SELECT
    '''Postgre'' ||+ NULL',
    'Postgre' ||+ NULL
UNION
SELECT
    'NULL ||+ ''SQL''',
    NULL ||+ 'SQL'
UNION
SELECT
    '''Postgre'' ||+ ''SQL''',
    'Postgre' ||+ 'SQL'
UNION
SELECT
    'NULL || NULL',
    NULL || NULL
UNION
SELECT
    'NULL ||+ NULL',
    NULL ||+ NULL
;

A seguir está mostrado o resultado do processamento do arquivo:

# psql -U teste -f concatena.sql -o concatena.out -q teste
# cat concatena.out
     Resultados das expressões
+---------------------+------------+
|      expressão      | resultado  |
+---------------------+------------+
| 'Postgre' || NULL   | (nulo)     |
| 'Postgre' ||+ 'SQL' | PostgreSQL |
| 'Postgre' ||+ NULL  | Postgre    |
| NULL || NULL        | (nulo)     |
| NULL ||+ 'SQL'      | SQL        |
| NULL ||+ NULL       |            |
+---------------------+------------+
(6 linhas)