Коварный SQL: запуск одного и того же запроса к таблицам с разными столбцами
Мы проводим время изо дня в день, отвечая на важные вопросы и придумывая наиболее разумные решения. Однако иногда возникает вопрос, который настолько чертовски… интересен, что даже если есть разумные решения или обходные пути, все равно кажется сложной задачей просто воспринимать запрос буквально.
Первый вопрос
Основной вопрос / запрос, который вдохновил эту статью, был внутренним вопросом о том, можно ли разработать запрос, работающий с таблицей / представлением, которые могут иметь разные столбцы в зависимости от версии PostgreSQL. Конкретные детали здесь не важны, но основная идея интригует. Итак, давайте доработаем наши инструменты и посмотрим, чего мы можем достичь.
Наивный подход
Всегда хорошо начинать с основ, поэтому давайте просто попробуем настроить / смоделировать нашу среду; мы хотим иметь две таблицы, которые отличаются добавлением столбца в одну. Мы будем использовать ту же базу данных, но две отдельные схемы, чтобы мы могли просто изменить search_path, чтобы имитировать выполнение запроса к двум отдельным таблицам.
Вот SQL:
CREATE SCHEMA db1;
CREATE SCHEMA db2;
CREATE TABLE db1.albums (album_name text, band_name text, release_date date, favorite_song text, rating int);
CREATE TABLE db2.albums (album_name text, band_name text, release_date date);
COPY db1.albums FROM STDIN;
Led Zeppelin Led Zeppelin 01-12-1969 Babe I'm Gonna Leave You 5
Led Zeppelin II Led Zeppelin 10-22-1969 The Lemon Song 5
Led Zeppelin III Led Zeppelin 10-05-1970 Since I've Been Loving You 5
The Wall Pink Floyd 11-30-1979 Comfortably Numb 5
Wish You Were Here Pink Floyd 09-12-1975 Wish You Were Here 5
Black Sabbath Black Sabbath 02-13-1970 N.I.B. 5
Paranoid Black Sabbath 09-18-1970 War Pigs 5
\.
INSERT INTO db2.albums SELECT album_name, band_name, release_date FROM db1.albums;
CREATE SCHEMA
CREATE SCHEMA
CREATE TABLE
CREATE TABLE
COPY 7
INSERT 0 7
Как видите, мы создали простую таблицу для хранения нашей базы данных, но в одной из таблиц есть дополнительные столбцы, которых нет в другой. Давайте попробуем простой подход к нашему запросу.
-- running the query against db1-version of the table
SET search_path = db1;
SELECT album_name,favorite_song,rating FROM albums WHERE band_name = 'Led Zeppelin';
album_name favorite_song rating
Led Zeppelin Babe I'm Gonna Leave You 5
Led Zeppelin II The Lemon Song 5
Led Zeppelin III Since I've Been Loving You 5
-- running the query against db2-version of the table
SET search_path = db2;
SELECT album_name,favorite_song,rating FROM albums WHERE band_name = 'Led Zeppelin';
ERROR: column "favorite_song" does not exist
LINE 1: SELECT album_name,favorite_song,rating FROM albums WHERE ban...
^
Следующая попытка
Неудивительно, тот же запрос не будет работать с таблицей, в которой отсутствует рассматриваемый столбец. Но можем ли мы сделать это немного хитрее?
Давайте немного подумаем: есть ли у нас тип данных в SQL, который может помочь нам оторваться от структурированной стороны имен столбцов и работать с вещами немного более свободно? Чтобы парсер не возненавидел нас, если мы упомянем несуществующий столбец? Ответ: да, их несколько, но нас волнует наш друг JSON! Существуют функции, которые позволяют преобразовывать произвольную строку таблицы в запись JSON, а затем мы можем управлять всей строкой как единым объектом, извлекать столбцы.
Давайте сделаем нашу следующую попытку, преобразовав строку в JSON и посмотрим, как динамически извлекать поля.
SET search_path = db1;
SELECT
json->>'album_name',
json->>'favorite_song',
json->>'rating'
FROM
(
SELECT to_json(albums) json
FROM albums
WHERE
band_name = 'Led Zeppelin'
) j
;
?column? ?column? ?column?
Led Zeppelin Babe I'm Gonna Leave You 5
Led Zeppelin II The Lemon Song 5
Led Zeppelin III Since I've Been Loving You 5SET search_path = db2;
SELECT
json->>'album_name',
json->>'favorite_song',
json->>'rating'
FROM
(
SELECT to_json(albums) json
FROM albums
WHERE
band_name = 'Led Zeppelin'
) j
;?column? ?column? ?column?
Led Zeppelin
Led Zeppelin II
Led Zeppelin IIIУстранение недостатков
У нас есть несколько недостатков, которые мы могли бы исправить; мы потеряли имена столбцов, а все типы данных - текст, плюс у нас нет значения по умолчанию для обработки вещей, когда мы работаем с отсутствующей таблицей. Было бы неплохо справиться с подобными вещами и получить результаты, максимально приближенные к «реальной таблице».
Таким образом, мы, безусловно, можем структурировать этот запрос, чтобы снова присвоить ему правильные метки, используя синтаксис AS <fieldname> для описаний полей. А поскольку мы знаем ожидаемые типы данных столбцов, мы всегда можем использовать приведение для правильной обработки. И поскольку оператор ->> возвращает значение NULL, если ключ не найден, мы можем использовать его для предоставления COALESCE() значений по умолчанию. Собирая все это вместе, для нашего примера данных должно работать что-то вроде этого:
SET search_path = db1;
SELECT
json->>'album_name' as album_name,
COALESCE(json->>'favorite_song', 'They all rock!') as favorite_song,
COALESCE((json->>'rating')::int, 99) as rating
FROM
(
SELECT to_json(albums) json
FROM albums
WHERE
band_name = 'Led Zeppelin'
) j
;
album_name favorite_song rating
Led Zeppelin Babe I'm Gonna Leave You 5
Led Zeppelin II The Lemon Song 5
Led Zeppelin III Since I've Been Loving You 5
SET search_path = db2;
SELECT
json->>'album_name' as album_name,
COALESCE(json->>'favorite_song', 'They all rock!') as favorite_song,
COALESCE((json->>'rating')::int, 99) as rating
FROM
(
SELECT to_json(albums) json
FROM albums
WHERE
band_name = 'Led Zeppelin'
) j
;
album_name favorite_song rating
Led Zeppelin They all rock! 99
Led Zeppelin II They all rock! 99
Led Zeppelin III They all rock! 99
Минусы
Конечно есть минусы такого подхода. Среди недостатков может потребоваться создание индекса выражения при вызове, чтобы сделать его эффективным для больших объемов данных; это плюс необходимость вручную настраивать типы данных и возвращаемые значения для преобразования в соответствующий тип данных, который вы ожидаете, делает этот подход нелепым для использования в действительности.
