On Thu, Aug 4, 2011 at 5:23 PM, David Johnston <polobo@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
