PostgreSQL | Если пустая строка, то конкатенация с заменой

PostgreSQL | Если пустая строка, то конкатенация с заменой

В нормальных языках программирования параметры функций могут вычисляться через вызовы других функций прямо в блоке объявления параметров. Это очень удобно.

Но в PostgreSQL и прочих SQL намудрили, накрутили и запутали большинство людей.

 

Задача

Нам нужно склеивать полные URL-адреса из составных частей, которые разложены по ячейкам таблиц.

Вот объекты для примера:

'{"s":"https", "d":"efim360", "r":"ru", "p":"/", "q":"n=1", "f":"sec"}'

'{"s":"https", "d":"efim360", "r":"ru", "p":"", "q":"", "f":""}'

это JSON-представление строк вида

https://efim360.ru/?n=1#sec

https://efim360.ru

Проблема тут в том, что следуя стандарту URI нельзя просто взять и склеить все кусочки воедино т. к. получится не то. Нам нужно учитывать существование URI-запросов и URI-фрагментов.

Вместо https://efim360.ru/?n=1#sec мы получим https://efim360.ru/n=1sec

А если будем принудительно приклеивать вопросы и решётки, то вместо https://efim360.ru получим https://efim360.ru?#

Это тоже неверно.

 

Нам нужно учитывать символы вопроса — ? и решётки — #.

Если после вопроса или решётки нет никаких других символов, то их нужно выкинуть из склеиваемой строки.

 

Решение

Сперва очень хочется начать мудрить с условными конструкциями вроде IF THEN. Но это тупик т. к. непонятно что куда встраивать.

Как это сделать просто при помощи PostgreSQL?

Нам нужно использовать регулярные выражения RegExp для сопоставления строк, а также две заранее подготовленные строки.

Синтаксис встроенной в PostgreSQL функции такой:

regexp_replace ( string text, pattern text, replacement text [, flags text ] )

Тут интуитивно всё понятно.

Есть какая то нужная нам строка. Под неё мы пишем шаблон регулярного выражения, который будет находить нам места сопоставления. И в конце заменяемое значение.

Флаг регулярного выражения мы не будем указывать т. к. нам не нужно будет сопоставлять всю строку под URI-запрос и под URI-фрагмент.

Все склеивания будем осуществлять в одной функции concat().

concat(p.path, regexp_replace('?'||q.query, '^[?]$', ''), regexp_replace('#'||f.fragment, '^[#]$', ''))

Самым важным элементом здесь является ‘^[?]$‘ и ‘^[#]$‘. Эти шаблоны говорят об Утверждениях. ^ — это граница начала строки. $ — это граница конца строки. Получается, что если при конкатенации ‘?’||q.query остаётся только ‘?’, значит это нужно заменить на пустую строку. И тогда общий concat не пострадает и правильно склеит строки (свои параметры).

 

Как это работает?

Правильно склеили пути с URI-фрагментами и URI-запросами в PostgreSQL
Правильно склеили пути с URI-фрагментами и URI-запросами в PostgreSQL

В пустом пути и в пути с одним слешом, не добавляется ?#.

Задача выполнена.

 

Информационные ссылки

Официальный сайт PostgreSQLhttps://www.postgresql.org

Официальный сайт WEB-оболочки pgAdminhttps://www.pgadmin.org

Команды SQLhttps://postgrespro.ru/docs/postgresql/14/sql-commands

Раздел «9.4. Строковые функции и операторы» — https://postgrespro.ru/docs/postgresql/14/functions-string