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.
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? |