DevGang
Авторизоваться

Коварный 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	5
SET 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

Минусы

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

#PostgreSQL #SQL
Комментарии
Чтобы оставить комментарий, необходимо авторизоваться

Присоединяйся в тусовку

Поделитесь своим опытом, расскажите о новом инструменте, библиотеке или фреймворке. Для этого не обязательно становится постоянным автором.

Попробовать

В подарок 100$ на счет при регистрации

Получить