postgresql - 在 PostgreSQL 中为交叉表动态生成列

Question

我正在尝试在 PostgreSQL 中创建 crosstab 查询，以便它自动生成 crosstab 列而不是对其进行硬编码。我编写了一个函数，它动态生成我的 crosstab 查询所需的列列表。想法是使用动态 sql 替换 crosstab 查询中此函数的结果。

我知道如何在 SQL Server 中轻松执行此操作，但我对 PostgreSQL 的有限了解阻碍了我在此方面的进步。我正在考虑将生成动态列列表的函数的结果存储到一个变量中，并使用它来动态构建 sql 查询。如果有人可以就此指导我，那就太好了。

-- Table which has be pivoted
CREATE TABLE test_db
(
    kernel_id int,
    key int,
    value int
);

INSERT INTO test_db VALUES
(1,1,99),
(1,2,78),
(2,1,66),
(3,1,44),
(3,2,55),
(3,3,89);


-- This function dynamically returns the list of columns for crosstab
CREATE FUNCTION test() RETURNS TEXT AS '
DECLARE
    key_id int;
    text_op TEXT = '' kernel_id int, '';
BEGIN
    FOR key_id IN SELECT DISTINCT key FROM test_db ORDER BY key LOOP
    text_op := text_op || key_id || '' int , '' ;
    END LOOP;
    text_op := text_op || '' DUMMY text'';
    RETURN text_op;
END;
' LANGUAGE 'plpgsql';

-- This query works. I just need to convert the static list
-- of crosstab columns to be generated dynamically.
SELECT * FROM
crosstab
(
    'SELECT kernel_id, key, value FROM test_db ORDER BY 1,2',
    'SELECT DISTINCT key FROM test_db ORDER BY 1'
)
AS x (kernel_id int, key1 int, key2 int, key3 int); -- How can I replace ..
-- .. this static list with a dynamically generated list of columns ?

Answer 1

为此，您可以使用提供的 C 函数 crosstab_hash。

手册在这方面不是很清楚。提到了at the end of the chapter on crosstab() with two parameters:

You can create predefined functions to avoid having to write out the result column names and types in each query. See the examples in the previous section. The underlying C function for this form of crosstab is named crosstab_hash.

以你的例子为例:

CREATE OR REPLACE FUNCTION f_cross_test_db(text, text)
  RETURNS TABLE (kernel_id int, key1 int, key2 int, key3 int)
  AS '$libdir/tablefunc','crosstab_hash' LANGUAGE C STABLE STRICT;

调用:

SELECT * FROM f_cross_test_db(
      'SELECT kernel_id, key, value FROM test_db ORDER BY 1,2'
     ,'SELECT DISTINCT key FROM test_db ORDER BY 1');

请注意，您需要为每个具有不同返回类型的 crosstab 函数创建一个不同的 crosstab_hash 函数。

相关:

• PostgreSQL row to columns

---

你的生成列列表的函数比较复杂，结果不正确(kernel_id后缺少int)，可以替换使用此 SQL 查询:

SELECT 'kernel_id int, '
       || string_agg(DISTINCT key::text, ' int, '  ORDER BY key::text)
       || ' int, DUMMY text'
FROM   test_db;

而且无论如何也不能动态使用。