sql - Calculate the sum of a values across multiple columns in a table in Postgres (plpgsql) -

i have table formatted:

fcst_month | idx1 | idx2 | idx3 | val1 | val2 | ... | valn 

i sum of of 'val's each fcst_month. seems way of doing transpose table using tablefunc crosstab() (https://www.postgresql.org/docs/9.3/static/tablefunc.html) , passing in column particular fcst_month, reading docs , other examples on so, i'm not understanding how use function achieve goal.

could give me example of crosstab() achieve or similar task? or perhaps suggest alternative achieving goal?

you can unpivot table using json functions row_to_json() , json_each_text(). additionaly, use with ordinality column numbers. example:

create table a_table (fcst_month int, val1 int, val2 int, val3 int); insert a_table values (1, 10, 20, 30), (2, 40, 50, 60);  select fcst_month, ordinality, key, value a_table, json_each_text(row_to_json(a_table)) ordinality;   fcst_month | ordinality |    key     | value  ------------+------------+------------+-------           1 |          1 | fcst_month | 1           1 |          2 | val1       | 10           1 |          3 | val2       | 20           1 |          4 | val3       | 30           2 |          1 | fcst_month | 2           2 |          2 | val1       | 40           2 |          3 | val2       | 50           2 |          4 | val3       | 60 (8 rows) 

now it's easy aggregate values in columns chosen position:

select fcst_month, sum(value::int) a_table, json_each_text(row_to_json(a_table)) ordinality ordinality > 1 group 1 order 1;   fcst_month | sum  ------------+-----           1 |  60           2 | 150 (2 rows)     

personally i'd use val1+ val2+ val3... 39 columns unless have deal dynamic, e.g. unknown number of columns.