sql - PostgreSQL:将寄存器的值获取为多行

Question

我正在使用 PostgreSQL 9.3 创建一个 Jasper 报告模板来生成 pdf 报告。我想创建包含多个列的不同表格的报告，所有这些都使用相同的模板。一种解决方案是将寄存器的值作为列名和每个 id 的值对获取。

例如，如果我有一个像这样的表:

id | Column1          | Column2     | Column3
-------------------------------------------------
1  | Register1C1      | Register1C2 | Register1C3

我想注册为:

Id | ColumnName | Value
-----------------------------
1  | Column1    | Register1C1
1  | Column2    | Register1C2
1  | Column3    | Register1C3

值列的数据类型可以变化!
是否可以？我该怎么做？

Answer 1

如果您的所有列共享相同的数据类型并且不必强制执行行的顺序:

SELECT t.id, v.*
FROM   tbl t, LATERAL (
   VALUES
     ('col1', col1)
   , ('col2', col2)
   , ('col3', col3)
      -- etc.
   ) v(col, val);

关于 LATERAL(需要 Postgres 9.3 或更高版本):

• What is the difference between LATERAL and a subquery in PostgreSQL?

将它与 VALUES 表达式组合:

• Crosstab transpose query request
• SELECT DISTINCT on multiple columns

对于不同的数据类型，共同点是text，因为每种类型都可以转换为text。另外，执行命令:

SELECT t.id, v.col, v.val
FROM   tbl t, LATERAL (
   VALUES
     (1, 'col1', col1::text)
   , (2, 'col2', col2::text)
   , (3, 'col3', col3::text)
     -- etc.
   ) v(rank, col, val)
ORDER  BY t.id, v.rank;

在 Postgres 9.4 或更高版本中，对多个数组使用新的 unnest():

SELECT t.id, v.*
FROM   tbl t, unnest('{col1,col2,col3}'::text[]
               , ARRAY[col1,col2,col3]) v(col, val);
           --  , ARRAY[col1::text,col2::text,col3::text]) v(col, val);

不同数据类型的注释替代方案。

Postgres 9.4 的完全自动化:

上面的查询对于动态列集的自动化很方便:

CREATE OR REPLACE FUNCTION f_transpose (_tbl regclass, VARIADIC _cols text[])
  RETURNS TABLE (id int, col text, val text) AS
$func$
BEGIN
   RETURN QUERY EXECUTE format(
     'SELECT t.id, v.* FROM %s t, unnest($1, ARRAY[%s]) v'
   , _tbl, array_to_string(_cols, '::text,') || '::text'))
-- , _tbl, array_to_string(_cols, ',')))  -- simple alternative for only text
   USING _cols;
END
$func$  LANGUAGE plpgsql;

调用 - 使用表名和任意数量的列名，任何数据类型:

SELECT * FROM f_transpose('table_name', 'column1', 'column2', 'column3');

弱点:列名列表对于 SQL 注入(inject)不是安全的。您可以改为从 pg_attribute 收集列名。示例:

• How to perform the same aggregation on every column, without listing the columns?