У нас есть строка:
'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 отвечает за получение результата для возврата из функции. Знак тильды «~» соответствует сопоставлению с учётом регистра символов. Одинарные кавычки ‘ обозначают границы шаблона регулярного выражения.
Шаблон начинается с символа «домика ^». Это называется Утверждение — оно обозначает левую границу строки то есть НАЧАЛО строки. Это значит, что символы после знака ^ должны быть в самом начале.
Круглые скобки также являются управляющими символами внутри шаблона регулярного выражения, они помогают создать Альтернативу, которая оформляется вертикальной линией. Альтернатива говорит, что ищем либо левую часть, либо правую.
Скрин:
Пример работы в текущем варианте:
— вызов функции
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’); — верно т.к. остальные условия не проверяются
Пример возврата:
Шаг № 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-адрес, а если условие ЛОЖНО, то передадим сообщение.
В таком виде мы трансформируем функцию, чтобы проверить работоспособность.
Шаг № 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$;
Скрин:
Давайте взглянем на содержимое нашей таблицы для вставки:
SELECT * FROM test1.tempurl;
Скрин:
Таблица пуста. Данных в ней нет.
Делаем вызовы:
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’); — если не верно, ОТКЛОНЕНИЕ
Результат вызовов:
Итоговые данные в таблице:
Информационные ссылки
PostgreSQL | Функция проверки наличия пробелов в строке данных или строке запроса с клиента
Официальный сайт PostgreSQL — https://www.postgresql.org
Команды SQL — https://postgrespro.ru/docs/postgresql/14/sql-commands
Команда CREATE FUNCTION — https://postgrespro.ru/docs/postgresql/14/sql-createfunction
Команда SELECT — https://postgrespro.ru/docs/postgresql/14/sql-select
Раздел «9.4. Строковые функции и операторы» — https://postgrespro.ru/docs/postgresql/14/functions-string
Раздел «9.7.3. Регулярные выражения POSIX» — https://postgrespro.ru/docs/postgresql/14/functions-matching#FUNCTIONS-POSIX-REGEXP
Раздел «43.6. Управляющие структуры» — https://postgrespro.ru/docs/postgresql/14/plpgsql-control-structures
Графический интерфейс pgAdmin — https://www.pgadmin.org