PostgreSQL | Проверка содержимого строки перед вставкой в таблицу по условию

У нас есть строка:

'https://efim360.ru'

Мы хотим проверять валидность данной строки к URL-адресу. Наш валидатор примитивный.

Мы будем просто проверять начинается ли наша строка на «http://» или «https://«.

Как это сделать?

Решение

Пусть у нас будет таблица tempurl в схеме test1:

CREATE TABLE IF NOT EXISTS test1.tempurl
(
id integer NOT NULL DEFAULT nextval('test1.tempurl_id_seq'::regclass),
url text COLLATE pg_catalog."default",
CONSTRAINT tempurl_pkey PRIMARY KEY (id)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE IF EXISTS test1.tempurl
OWNER to postgres;

В ней 2 столбца. Один с идентификаторами АВТОИНКРЕМЕНТАМИ, второй с валидными URL-адресами. Изначально она пустая. Мы создали её в pgAdmin, который дополнительно создал Последовательность для этой таблицы:

CREATE SEQUENCE IF NOT EXISTS test1.tempurl_id_seq
INCREMENT 1
START 1
MINVALUE 1
MAXVALUE 2147483647
CACHE 1
OWNED BY tempurl.id;

ALTER SEQUENCE test1.tempurl_id_seq
OWNER TO postgres;

Есть два способа решения:

Мы устанавливаем ограничения на уровне таблицы.
Мы пишем функцию, которая делает проверку перед вставкой.

Мы выбираем второй вариант. У нас будет примитивный случай, «забуриваться» в детали и правильность мы не будем. Чисто для примера.

Шаг № 1 — Проверяем, что строка начинается НА

Функция g1 будет принимать один параметр — строку (строковый тип данных). Передавать в функцию будем URL-адрес, который хотим проверить.

Внутри будет производиться сопоставление. Проще всего это делать регулярным выражением, которые работают практически во всех языках программирования одинаково.

CREATE OR REPLACE FUNCTION test1.g1(str text)
RETURNS boolean
LANGUAGE 'sql'
AS $BODY$
  SELECT str ~ '^(https://|http://)'
$BODY$;

Внутри мы используем проверку строк регулярными выражениями POSIX.

В команде — select str ~ ‘^(https://|http://)’ — слово select отвечает за получение результата для возврата из функции. Знак тильды «~» соответствует сопоставлению с учётом регистра символов. Одинарные кавычки ‘ обозначают границы шаблона регулярного выражения.

Шаблон начинается с символа «домика ^». Это называется Утверждение — оно обозначает левую границу строки то есть НАЧАЛО строки. Это значит, что символы после знака ^ должны быть в самом начале.

Круглые скобки также являются управляющими символами внутри шаблона регулярного выражения, они помогают создать Альтернативу, которая оформляется вертикальной линией. Альтернатива говорит, что ищем либо левую часть, либо правую.

Скрин:

Создали функцию, проверяющую начинается ли строка на http или https - PostgreSQL — Создали функцию, проверяющую начинается ли строка на http или https — PostgreSQL

Пример работы в текущем варианте:

— вызов функции
select test1.g1(‘https://efim360.ru’); — верно
select test1.g1(‘hрttps://efim360.ru’); — не верно
select test1.g1(‘https:://efim360.ru’); — не верно
select test1.g1(‘https:\/efim360.ru’); — не верно
select test1.g1(‘https:///efim360.ru’); — верно т.к. остальные условия не проверяются
select test1.g1(‘http:///efim360.ru’); — верно т.к. остальные условия не проверяются

Пример возврата:

Функция проверки начинания строки на http и https вернула true - PostgreSQL — Функция проверки начинания строки на http и https вернула true — PostgreSQL

Шаг № 2 — Создаём конструкцию условия для вставки

Немного поправим нашу функцию и назовём её g2, чтобы не дублировать информацию:

-- Меняем язык с 'sql' на 'plpgsql'
-- Добавляем СТАРТ (BEGIN) и ФИНИШ (END)
-- Добавляем (IF-THEN-END IF)
CREATE OR REPLACE FUNCTION test1.g2(str text)
RETURNS text
LANGUAGE 'plpgsql'
AS 
$BODY$
  BEGIN
    IF str ~ '^(https://|http://)'
    THEN RETURN str;
    END IF;
    RETURN 'URL-адрес не валидный';
  END;
$BODY$;

Первым делом мы меняем язык с ‘sql’ на ‘plpgsql’. Это нужно для того, что иметь возможность использовать УСЛОВНЫЕ конструкции.

Теперь внутри тела функции $BODY$ мы должны записать в каким месте начинается логика работы функции, а в каком заканчивается. За это отвечают BEGIN END. Теперь внутри можно использовать (IF—THEN—END IF). Условие IF должно выдавать true или false, тогда у нас появится ветвление в программе.

Управляющая Структура RETURN отвечает за МГНОВЕННОЕ ЗАВЕРШЕНИЕ функции и возврат содержимого, которое находится справа от оператора RETURN. В нашем случае если условие ВЕРНО, то мы просто вернём наш передаваемый URL-адрес, а если условие ЛОЖНО, то передадим сообщение.

В таком виде мы трансформируем функцию, чтобы проверить работоспособность.

Дополнили функцию условной конструкцией с ветвлением - PostgreSQL — Дополнили функцию условной конструкцией с ветвлением — PostgreSQL

Шаг № 3 — Добавляем возможность вставки значения в таблицу по условию

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

-- Добавляем вставку в нужную таблицу
CREATE OR REPLACE FUNCTION test1.g3(str text)
RETURNS text
LANGUAGE 'plpgsql'
AS $BODY$
  BEGIN
    IF str ~ '^(https://|http://)'
    THEN INSERT INTO test1.tempurl (url) VALUES (str);
    RETURN concat(str, ': ДОБАВЛЕН');
    END IF;
    RETURN concat(str, ': ОТКЛОНЁН');
  END;
$BODY$;

Скрин:

Функция, проверяющая строку по условию, перед вставкой в таблицу - PostgreSQL — Функция, проверяющая строку по условию, перед вставкой в таблицу — PostgreSQL

Давайте взглянем на содержимое нашей таблицы для вставки:

SELECT * FROM test1.tempurl;

Скрин:

Таблица tempurl до вставок - пустая - PostgreSQL — Таблица tempurl до вставок — пустая — PostgreSQL

Таблица пуста. Данных в ней нет.

Делаем вызовы:

select test1.g3(‘https://efim360.ru’); — если верно, ВСТАВКА
select test1.g3(‘hрttps://efim360.ru’); — если не верно, ОТКЛОНЕНИЕ
select test1.g3(‘http://efim360.ru’); — если верно, ВСТАВКА
select test1.g3(‘https:://efim360.ru’); — если не верно, ОТКЛОНЕНИЕ

Результат вызовов: