Моя любимая функция 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'])
превращается в такую выборку:
ШТО ЭТО 😀
Это какой-то кошмар)) Плагин подсветки синтаксиса перестал работать. Тружусь над корректировкой.