sql - How to unpivot a table in PostgreSQL

Question

I am having difficulties writing a Postgres function, as I am not familiar with it. I have multiple tables to import into Postgres with this format:

id | 1960 | 1961 | 1962 | 1963 | ...
____________________________________
 1    23     45     87     99
 2    12     31    ...

which I need to convert into this format:

id | year |?value
_________________
 1   1960    23
 1   1961    45
 1   1962    87
 ...
 2   1960    12
 2   1961    31
 ...

I would imagine the function too to read like this:

SELECT all-years FROM imported_table;
CREATE a new_table;
FROM min-year TO max-year LOOP
     EXECUTE "INSERT INTO new_table (id, year, value) VALUES (id, year, value)";
END LOOP;

However, I'm having real trouble writing the nitty-gritty details for this. Would be easier for me to do that in PHP, but I am convinced that it's cleaner to do it directly in a Postgres-function.

The years (start and end) vary from table to table. And sometimes, I can even have years only for every fifth year or so ...

Answer 1

PostgreSQL 9.3 offers as neat JSON functions which can be used for such tasks without defining new functions or knowing a number of columns.

SELECT id, (k).key as year, (k).value as value FROM
  (SELECT j->>'id' as id, json_each_text(j) as k
    FROM (
       SELECT row_to_json(tbl) as j FROM tbl) 
    as q)
    as r
WHERE (k).key <> 'id';

http://sqlfiddle.com/#!15/1714b/13