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.
