test objects:
create table t ( sun numeric, mon numeric, tue numeric, wed numeric, thu numeric, fri numeric ); insert into t(sun, mon, tue, wed, thu, fri) values(1.24, 1.11, 4.51, 3.21, 2.21, 1.01);
@Unreason alternative answer without union :
select day[i], amount[i] from ( select generate_series(1,6) as i, array['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri'] as day, array[sun, mon, tue, wed, thu, fri] as amount from t ) z;
If you need to be more general, you can do something like this:
create or replace function unpivot(t) returns setof record language plpgsql immutable strict as $$ declare q record; r record; begin for q in ( select attname, attnum from pg_attribute where attnum>0 and attrelid = ( select oid from pg_class where relname = 't' ) ) loop for r in execute 'select '''||q.attname||'''::text, '|| '('||$1::text||'::t).'||q.attname||'::numeric' loop return next r; end loop; end loop; return; end;$$; select * from unpivot((select row(t.*)::t from t)) as foo(day text, amount numeric);
you can be a little neat in 8.4 with the using clause in execute , but I can't verify this since I'm at 8.3