UNNEST составной массив в строки и столбцы в Postgres

Постгрес 11.7.

Я пытаюсь распаковать массив, в котором каждый элемент имеет несколько элементов, и, похоже, не могу правильно понять синтаксис. Я надеюсь, что кто-то может указать, что мне не хватает. Вот пример:

select
unnest(array[

                 ('Red Large Special',     1),
                 ('Blue Small',            5),
                 ('Green Medium Special', 87)

              ]) as item_list

Это то, что я хочу:

item_name               item_id  
Red Large Special       1
Blue Small              5
Green Medium Special   87

Вот что я получаю:

base_strings
("Red Large Special",1)
("Blue Small",5)
("Green Medium Special",87)

Я считаю, что мне нужен список спецификаций столбцов, что-то вроде этого:

select * from
unnest(array[

                 ('Red Large Special',    1),
                 ('Blue Small',        5),
                 ('Green Medium Special', 87)

              ]) AS item_list(item_name citext, item_id int4)

Что я получаю:

ERROR:  function return row and query-specified return row do not match
DETAIL:  Returned type unknown at ordinal position 1, but query expects citext. (Line 9)

Я могу заставить его работать, если формально объявлю пользовательский составной тип:

CREATE TYPE item_details AS (
   item_name citext,
   item_id   int4);

select * from
unnest(array[

                 ('Red Large Special',    1),
                 ('Blue Small',        5),
                 ('Green Medium Special', 87)

              ]::item_details[]) as item_list

Это правильно:

item_name             item_id
Red Large Special     1
Blue Small            5
Green Medium Special  87

Есть ли способ получить тот же результат без объявления типа? Я ищу решение, в котором я могу определить тип на лету. Я почти уверен, что делал это в Postgres в прошлом, но, может быть, это было с JSONB?

Я проконсультировался с Fine Documentation по выражениям, возвращающим таблицу, но не смог следовать ей. На самом деле там нет примера, я не могу экстраполировать из сводки по грамматике.

https://www.postgresql.org/docs/current/queries-table-expressions.html

Следовать за

Два отличных ответа, которые мешают мне гоняться за собственным хвостом. В этом случае задача состоит в том, чтобы открыть некоторые функции для нескольких клиентов, поэтому мне, вероятно, лучше использовать JSON, чем синтаксис массива, характерный для Postgres. @a_horse_with_no_name приводит меня к такому коду, начиная с текста JSON:

with expanded_data AS (
 select * 
   from json_to_recordset(
        '[
             {"base_text":"Red Large Special","base_id":1},
             {"base_text":"Blue Small","base_id":5},
             {"base_text":"Green Medium Special","base_id":87}
         ]')
      AS unpacked (base_text citext, base_id citext)
 )

select base_text,
       base_id

  from expanded_data

postgresql unnest
person Morris de Oryx    schedule 08.05.2020    source источник

Ответы (2)


arrow_upward
2
arrow_downward

Один из способов, который я могу придумать, - преобразовать его в массив jsonb:

select item ->> 'f1' as item_name, 
       (item ->> 'f2')::int as item_id 
from jsonb_array_elements(to_jsonb(array[
                 ('Red Large Special',     1),
                 ('Blue Small',            5),
                 ('Green Medium Special', 87)
              ])) t(item)
person a_horse_with_no_name    schedule 08.05.2020

arrow_upward
2
arrow_downward

Поскольку строковые константы на самом деле имеют тип unknown, вам необходимо явно указать желаемый тип:

# select * from
unnest(array[
                 ('Red Large Special'::citext,    1),
                 ('Blue Small'::citext,        5),
                 ('Green Medium Special'::citext, 87)
              ]) AS item_list(item_name citext, item_id int4);
┌──────────────────────┬─────────┐
│      item_name       │ item_id │
├──────────────────────┼─────────┤
│ Red Large Special    │       1 │
│ Blue Small           │       5 │
│ Green Medium Special │      87 │
└──────────────────────┴─────────┘
person Abelisto    schedule 08.05.2020
comment
Потому что строковые константы на самом деле имеют неизвестный тип — это одна из вещей, которые мне не очень нравятся (и не понятны) в Postgres :( - person a_horse_with_no_name; 08.05.2020
comment
@a_horse_with_no_name Возможно, у этой функции больше преимуществ, чем недостатков. - person Abelisto; 08.05.2020