Моя любимая функция PostgreSQL — unnest (ссылка на документацию)! Она такая простая, но такая классная 😀
Она очень пригодится, если нужно вставить сразу несколько записей за один присест. Допустим, у одной фирмы есть несколько номеров телефонов и,чтобы не перечислять вставку этих данных через запятую, дублируя id фирмы, можно использовать unnest — элегантное решение!
Пусть у нас есть таблица telephone_numbers:
[pastacode lang=»sql» manual=»create%20table%20public.telephone_numbers%20(%0Aid%20integer%20not%20null%2C%0Anumber%20character%20varying(100)%20not%20null%0Aconstraint%20numbers_pkey%20primary%20key%20(id%2C%20number))» message=»» highlight=»» provider=»manual»/]
Для фирмы с id = 5 надо вставить 5 номеров телефона: 123, 456, 789, 147, 258.
Через перечисление:
[pastacode lang=»sql» manual=»insert%20into%20telephone_numbers(id%2C%20number)%0Avalues%20(5%2C%20’123′)%2C%C2%A0(5%2C%20’456′)%2C%C2%A0(5%2C%20’789′)%2C%C2%A0(5%2C%20’258′)%2C%C2%A0(5%2C%20’147′)%3B» message=»» highlight=»» provider=»manual»/]
Муторно же 🙂
Используя unnest(array):
[pastacode lang=»sql» manual=»insert%20into%20telephone_numbers(id%2C%20number)%0Aselect%205%2C%20unnest(array%5B’123’%2C%20’456’%2C%20’789’%2C%20’258’%2C%20’147’%5D)%3B» message=»» highlight=»» provider=»manual»/]
Функция раскладывает значения, указанные в массиве array[] на строки, таким образом, автоматически формируя вставку значений через перечисление.
Это очень удобно, когда нам необходимо вставить за раз несколько массивов данных. Тогда они будут последовательно связаны друг с другом. Например, следующая конструкция
[pastacode lang=»sql» manual=»insert%20into%20products(product_code%2C%20price)%0Aselect%20unnest(array%5B111%2C%20222%2C%20333%2C%20444%5D)%2C%20unnest(array%5B1.25%2C%200.50%2C%201.50%2C%202%5D)%3B» message=»» highlight=»» provider=»manual»/]
будет эквивалентна записи:
[pastacode lang=»sql» manual=»insert%20into%20products(product_code%2C%20price)%20%0Avalues%20(111%2C%201.25)%2C%20(222%2C%200.50)%2C%20(333%2C%201.50)%2C%20(444%2C%202)%3B» message=»» highlight=»» provider=»manual»/]
Если в таком перечислении будет неравное количество элементов, недостающие будут заменены на null.
Очень удобно использование unnest при формировании скрипта в коде. Например, в Qt:
[pastacode lang=»cpp» manual=»QSqlQuery%20query%3B%0AQStringList%20productCodes%2C%20prices%3B%0AproductCodes%20%3C%3C%20%22111%22%20%3C%3C%20%22222%22%20%3C%3C%20%22333%22%20%3C%3C%20%22444%22%3B%0Aprices%20%3C%3C%20%221.25%22%20%3C%3C%20%220.50%22%20%3C%3C%20%221.50%22%20%3C%3C%20%222%22%3B%0Aif%20(!query.exec(QString(%22insert%20into%20products(product_code%2C%20price)%20%22%0A%09%09%09%09%09%09%22select%20unnest(array%5B%251%5D)%2C%20unnest(array%5B%252%5D)%22)%0A%09%09%09%20%20%20.arg(productCodes.join(%22%2C%20%22)%2C%20prices.join(%22%2C%20%22))))%0A%09qDebug()%20%3C%3C%20query.lastError().text()%3B» message=»» highlight=»» provider=»manual»/]
Перечислять массивы внутри функции можно через запятую. И вот такая инструкция
[pastacode lang=»sql» manual=»select%20*%20from%20%0Aunnest(array%5B1%2C2%2C3%5D%2C%20array%5B’a’%2C%20’b’%2C%20’c’%5D)» message=»» highlight=»» provider=»manual»/]
превращается в такую выборку:
