PostgreSQL, Qt

PostgreSQL: unnest(array)

Моя любимая функция PostgreSQL — unnest (ссылка на документацию)! Она такая простая, но такая классная 😀

Она очень пригодится, если нужно вставить сразу несколько записей за один присест. Допустим, у одной фирмы есть несколько номеров телефонов и, чтобы не перечислять вставку этих данных через запятую, дублируя id фирмы, можно использовать unnest — элегантное решение!

Пусть у нас есть таблица telephone_numbers:

create table public.telephone_numbers (
id integer not null, 
number character varying(100) not null, 
constraint numbers_pkey primary key (id))

Для фирмы с id = 5 надо вставить 5 номеров телефона: 123, 456, 789, 147, 258.

Через перечисление:

insert into telephone_numbers(id, number)
values (5, '123'), (5, '456'), (5, '789'), 
(5, '258'), (5, '147')

Муторно же 🙂

Используя unnest(array):

insert into telephone_numbers(id, number)
select unnest(array[[5, '123'], [5, '456'], [5, '789'], [5, '258'], [5, '147']])

Функция раскладывает значения, указанные в массиве array[] на строки, таким образом, автоматически формируя вставку значений через перечисление.

Это очень удобно, когда нам необходимо вставить за раз несколько массивов данных. Тогда они будут последовательно связаны друг с другом. Например, следующая конструкция

insert into products(product_code, price)
select unnest(array['20222', '20333', '20444']), 
unnest(array[1.25, 200.50, 201.50, 202])

будет эквивалентна записи:

insert into products(product_code, price)
values ('20222', 1.25), ('20333', 200.05), 
('20444', 201.50), (null, 202)

Если в таком перечислении будет неравное количество элементов, недостающие будут заменены на null.

Очень удобно использование unnest при формировании скрипта в коде. Например, в Qt:

QSqlQuery query;
QStringList productCodes, prices;
productCodes << "20222" << "20333" << "20444";
prices << "1.25" << "200.05" << "201.50" << "202";
if (!query.exec(QString("
  insert into products(product_code, price) 
  select unnest(array['%1']), unnest(array[%2])")
          .arg(productCodes.join("', '"))
          .arg(prices.join(", "))))
   qDebug() << query.lastError().text();

Перечислять массивы внутри функции можно через запятую. И вот такая инструкция

select * from unnest(array[1,2,3], array['a', 'b', 'c'])

превращается в такую выборку:

unnest

PostgreSQL: unnest(array): 2 комментария

    1. Это какой-то кошмар)) Плагин подсветки синтаксиса перестал работать. Тружусь над корректировкой.

Оставьте своё мнение...

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.