在PostgreSQL中为交叉表动态生成列 [英] Dynamically generate columns for crosstab in PostgreSQL

问题描述

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

I am trying to create crosstab queries in PostgreSQL such that it automatically generates the crosstab columns instead of hardcoding it. I have written a function that dynamically generates the column list that I need for my crosstab query. The idea is to substitute the result of this function in the crosstab query using dynamic sql.

我知道如何在SQL Server中轻松地做到这一点，但是我对PostgreSQL的有限了解阻碍了我的进步.我正在考虑将将列的动态列表生成的函数的结果存储到变量中，并使用该结果来动态构建sql查询.有人可以指导我做同样的事情太好了.

I know how to do this easily in SQL Server, but my limited knowledge of PostgreSQL is hindering my progress here. I was thinking of storing the result of function that generates the dynamic list of columns into a variable and use that to dynamically build the sql query. It would be great if someone could guide me regarding the same.

-- 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 ?

推荐答案

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

You can use the provided C function crosstab_hash for this.

这方面的手册不是很清楚.在crosstab()章节的结尾处提到了，其中有两个参数:

The manual is not very clear in this respect. It's mentioned at the end of the chapter on crosstab() with two parameters:

您可以创建预定义函数以避免写出 每个查询中的结果列名称和类型.请参阅 上一节.此crosstab形式的基础C函数 被命名为crosstab_hash.

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函数.

Note that you need to create a distinct crosstab_hash function for every crosstab function with a different return type.

相关:

• PostgreSQL行到列

用于生成列列表的函数相当复杂，结果不正确(kernel_id后缺少int)，可以用以下SQL查询替换它:

Your function to generate the column list is rather convoluted, the result is incorrect (int missing after kernel_id), it can be replaced with this SQL query:

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

而且它还是不能动态使用.

And it cannot be used dynamically anyway.

这篇关于在PostgreSQL中为交叉表动态生成列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！