sql - 如何在 PostgreSQL 中对表进行逆透视

Question

我在编写 Postgres 函数时遇到困难，因为我不熟悉它。我有多个表以这种格式导入到 Postgres 中:

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

我需要将其转换成这种格式:

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

我想这个函数也可以这样读:

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;

但是，我在为此编写具体细节时遇到了真正的麻烦。在 PHP 中执行此操作对我来说会更容易，但我相信直接在 Postgres 函数中执行此操作更清晰。

年份(开始和结束)因表而异。有时，我什至可以每五年左右就有几年......

Answer 1

PostgreSQL 9.3 提供简洁的 JSON functions无需定义新函数或不知道列数即可用于此类任务。

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