Re: Is there a better way to unnest an entire row?

On Thu, Aug 4, 2011 at 5:23 PM, David Johnston <polobo(at)yahoo(dot)com> wrote:
> Currently I have a de-normalized table with two sets of “records” embedded
> (i.e.,  [id, item1_name, item1_amount, item2_name, item2_amount]).  My goal
> is to output two records (i.e., [id, item_name, item_amount]) into an
> intermediate result and then remove any records where item_name IS NULL or
> “blank”.  There are many possible solutions but I am hoping to solicit some
> fairly succinct (syntax-wise) possibilities.
>
>
>
> I can readily do this using self-joins and UNION constructs but I was to
> basically trying to write a query that will only access each record once.
> My gut says that ARRAYS are going to be part of the solution so I tried
> this:
>
>
>
> SELECT unnest(arr_id), unnest(arr_name), unnest(arr_value)
>
> FROM (
>
>                 SELECT ARRAY[id, id] AS arr_id,
>
>                                ARRAY[item1_name, item2_name] AS arr_name,
>
>                                ARRAY[item1_value, item2_value] AS arr_value
>
>                 FROM table
>
> ) arrayed;
>
>
>
> It appears you cannot “unnest” a record type so I need an unnest(…) call for
> each ARRAY I build in the sub-query.

sure you can. it just can't be an anonymous type.

postgres=# create type foo_t as (a int, b text);
CREATE TYPE

postgres=# select unnest(array[(1, 'abc'), row(2, 'def')]);
unnest
---------
(1,abc)
(2,def)

postgres=# select (unnest(array[(1, 'abc'), row(2, 'def')])).*;
ERROR: record type has not been registered

postgres=# select (unnest(array[(1, 'abc'), row(2, 'def')]::foo_t[])).*;
a | b
---+-----
1 | abc
2 | def
(2 rows)

I think that's the 'right' way to do it and if so it makes your question moot.

merlin