Is there a better way to unnest an entire row?

From: "David Johnston" <polobo(at)yahoo(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Is there a better way to unnest an entire row?
Date: 2011-08-04 22:23:24
Message-ID: 00f401cc52f5$20d38520$627a8f60$@yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

I am currently deploying 9.0 but solutions that are only possible on 9.1 are
welcomed as well.

For my current situation this query form will work just fine but I am
curious about what others would do with this. Also, I can ensure that each
ARRAY in the sub-query only has the same number elements - it seems that
using "unnest()" in situations where the ARRAY sizes could vary would be
problematic but am I relying upon behavior of "unnest" that I should not be?

Thanks,

David J.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Johnston 2011-08-04 22:54:56 Re: Is there a better way to unnest an entire row?
Previous Message Igor Neyman 2011-08-04 18:58:10 Re: query to get the list of key (reserverd) words?