4

Postgres 11.7.

I'm trying to unpack an array where each item has multiple elements, and can't seem to get the syntax right. I'm hoping that someone can point out what I'm missing. Here's an example:

select
unnest(array[

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

              ]) as item_list

This is what I want:

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

This is what I get:

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

I believe that I need a column specification list, something like this:

select * from
unnest(array[

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

              ]) AS item_list(item_name citext, item_id int4)

What I get is:

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)

I can get it to work if I formally declare a custom, composite type:

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

This is right:

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

Is there any way to get the same result without declaring a type? I'm looking for a solution where I can define the type on-the-fly. I'm pretty sure that I've done this in Postgres in the past, but maybe it was with JSONB?

I've consulted the Fine Documentation on table returning expressions, but couldn't follow it. There's not really an example there, I I'm unable to extrapolate from the grammar summary.

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

Follow-up

Two great answers that stop me from chasing my own tail. In this case, the task is to open up some functionality to multiple clients, so I'm probably better off using JSON than the Postgres-specific array syntax. @a_horse_with_no_name leads me to this kind of code, starting from JSON text:

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
Morris de Oryx
  • 1,857
  • 10
  • 28

3 Answers3

5

Because string constants actually have unknown type you need to specify the desired type explicitly:

# 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 │
└──────────────────────┴─────────┘
Abelisto
  • 14,826
  • 2
  • 33
  • 41
  • "*Because string constants actually have unknown type*" - this is one of the things I don't really like (and don't understand) about Postgres :( –  May 08 '20 at 12:43
  • 1
    @a_horse_with_no_name Maybe advantages more then disadvantages of this feature. – Abelisto May 08 '20 at 12:47
4

One way I can think of, is to convert it into a jsonb array:

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)
2

I had has the some problem and ended here looking a solution, i read these useful answer and finally I guess we have it :

select * from
unnest(array[
    ('Red Large Special'::text,     1),
    ('Blue Small'::text,            5),
    ('Green Medium Special'::text, 87)
]) as item_list(name text, id int)

No json required and also no need to declare a type ;) Morris de Oryx you were sooo close!

DO MIND that explicit casting to ::text is mandatory, if not you will experience some trouble as text without ::text will be resolved as unknow by postgres's engine.

Victor
  • 3,841
  • 2
  • 37
  • 63