Пользовательский оператор PostgreSQL сравнивает varchar и integer

Question

- PostgreSQL 9.6.2 на x86_64-pc-linux-gnu, скомпилированный gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-17), 64-битный.

- Установлено из официального репозитория.

- Никаких изменений в postgresql.conf .

- CentOS релиз 6.8.

- Пользователь: postgres.

- Используется pgAdmin3 LTS от BigSQL.

- Никакого необычного входа на сервер.

У меня много вопросов.

в этом случае мне нужно сравнить символьный тип данных (может быть поле таблицы) с целочисленным значением.

- Результат верный или ложный

select '10' = 10; 
select '10' = '10'; 
select '10'::character varying = '10'::character varying; 
select '10'::character varying = 'foo bar'; 
select '10'::character varying = 'foo bar'::character varying; 
select 'foo bar' = 'foo bar'; 
select '10'::character varying = '10';

- Результат "оператор не существует: переменная буква = целое число"

select '10'::character varying = 10; 

поэтому я создаю пользовательский оператор для сравнения символов и целых чисел.

Шаг 1: создать простую функцию

CREATE OR REPLACE FUNCTION public.is_equal_char_int(character varying, integer) RETURNS boolean AS 
$BODY$ 
BEGIN 
    IF $1 = $2::character varying THEN
        RETURN TRUE;
    ELSE 
        RETURN FALSE; 
    END IF;
End;
$BODY$ 
LANGUAGE plpgsql VOLATILE COST 100;

шаг 2: создать новый оператор

CREATE OPERATOR public.=( 
PROCEDURE = is_equal_char_int,
LEFTARG = character varying,
RIGHTARG = integer);

так что я решил свою проблему и

select '10'::character varying = 10;

вернуть истинное значение.

и новая проблема: когда я сравниваю переменное значение символа с неизвестным значением типа данных, postgresql использует мой пользовательский оператор.

select '10'::character varying = 'foo bar';

результат:

неверный синтаксис ввода для целого числа: "foo bar"

select pg_typeof('foo bar');

вернуть неизвестный тип данных.

и на следующем шаге я создаю новый оператор для сравнения символов и неизвестных типов данных.

Шаг 1:

CREATE OR REPLACE FUNCTION public.is_equal_char_unknown(character varying, unknown)
RETURNS boolean AS
$BODY$
BEGIN
IF $1 = $2::character varying THEN
    RETURN TRUE;
ELSE
    RETURN FALSE;
END IF;
End;
$BODY$
LANGUAGE plpgsql VOLATILE COST 100;

шаг 2:

CREATE OPERATOR public.=( 
PROCEDURE = is_equal_char_unknown,
LEFTARG = character varying,
RIGHTARG = unknown);

когда я бегу

select '10'::character varying = 'foo bar';

Я даю

ОШИБКА: оператор не уникален: символ меняется = неизвестно.

Так что я в дыре.

Answer 1

Чтобы понять, как выполняется разрешение типов для операторов в PostgreSQL, ознакомьтесь с правилами разрешения типов операторов в документации.

В вашем особом случае после шага 3.a остаются следующие операторы:

• Ваш пользовательский оператор (character varying = integer).

• character = character (неявное преобразование из character varyingв character).

• name = name (неявное преобразование из character varyingв name).

• text = text (неявное преобразование из character varyingв text).

Правило 3.c затем выбирает ваш оператор, потому что он единственный с точным совпадением типов. Без вашего оператора, шаг 3.d выбрал бы text = text, так как text является единственным предпочтительным типом строковой категории.

В настоящий момент вы обнаруживаете, почему определенные операторы не определены в PostgreSQL, а именно, что определение новых операторов сравнения для новых комбинаций типов приводит к неоднозначностям, которые приводят к ошибкам, поскольку PostgreSQL не может решить, какой оператор использовать.

В основе проблемы лежит способность PostgreSQL перегружать операторы, то есть иметь несколько операторов с одинаковым именем. Тем не менее, это хорошая функция, и система кастов и операторов была тщательно сбалансирована, чтобы опыт был как можно лучше. unknown Тип также является частью этой системы.

Другими словами, PostgreSQL пытается угадать, что вы имеете в виду, но это не всегда возможно. Если вы хотите сравнить (не unknown) строка и число, что вы хотите? Должны ли они сравниваться как числа или как строки? Должен '010' быть таким же, как 10 или нет? PostgreSQL не знает, что вы имеете в виду, и сдается.

Answer 2

Может быть другой вариант, определяющий, как должно быть выполнено приведение между varchars и numerics:

      CREATE CAST (VARCHAR AS NUMERIC) WITH INOUT AS IMPLICIT;

Это позволит проводить сравнения следующим образом:

      SELECT '1'::character varying = 1::int;
> true

SELECT '01'::character varying = 1::int;
> true

SELECT '2'::character varying = 1::int;
> false

select '10'::character varying = 'foo bar';
> false

Подробнее о создании приведения в postgresql здесь:https://www.postgresql.org/docs/current/sql-createcast.html .