在PostgreSQL中从函数创建动态表格

Question

在PostgreSQL中从函数创建动态表格

5

我有一些表格数据，

select * from tbltaxamount ;
 id |   taxname   | taxinfoid | taxvalue | taxamt | zoneid | invoiceid | transid 
----+-------------+-----------+----------+--------+--------+-----------+---------
  1 | Service Tax |         0 |     0.00 |  28.69 |      2 |       119 |      -1
  2 | ABC Tax     |         0 |     0.00 |  25.78 |      2 |       119 |      -1

现在，我该如何使用PostgreSQL的任何函数来获得以下结果呢？

invoiceid | Service Tax | ABC Tax
----------+-------------+--------
      119 |       28.69 |  25.78

- Bhavik Ambani

3个回答

5

尝试多次后，我创建了下面的函数来动态创建表格，并显示如上所示的记录。

CREATE OR REPLACE FUNCTION taxamount() RETURNS void as $$
DECLARE
        columnNames RECORD;
    invoiceids RECORD;
BEGIN
    FOR columnNames IN  SELECT * from pg_tables where tablename = 'tmptable'
        LOOP
            DROP TABLE tmptable ;        
        END LOOP;
    CREATE TABLE tmptable (invoiceid integer PRIMARY KEY);
    FOR columnNames IN SELECT distinct(replace(taxname,' ','')) as taxnames from tbltaxamount
        LOOP
                EXECUTE 'ALTER TABLE tmptable ADD ' || columnNames.taxnames || ' numeric(9,2) DEFAULT 0';
        END LOOP;
    FOR invoiceids IN SELECT distinct(invoiceid) from tbltaxamount
    LOOP
        EXECUTE 'INSERT INTO tmptable (invoiceid) VALUES (' || invoiceids.invoiceid || ')';
    END LOOP;
    FOR invoiceids IN SELECT * from tbltaxamount
    LOOP
        EXECUTE 'UPDATE tmptable SET ' || replace(invoiceids.taxname,' ','') || ' = ' || invoiceids.taxamt  || ' WHERE invoiceid = ' || invoiceids.invoiceid;
    END LOOP ;
RETURN;
END;
$$ LANGUAGE plpgsql;

- Bhavik Ambani

2

请检查上述代码，并在有更好的解决方案时提出建议。 - Bhavik Ambani

1

可能长这样：

SELECT invoiceid
      ,sum(CASE WHEN taxname = 'Service Tax' THEN taxamt ELSE 0 END) AS "Service Tax"
      ,sum(CASE WHEN taxname = 'ABC Tax'     THEN taxamt ELSE 0 END) AS "ABC Tax"
FROM   tbltaxamount 
GROUP  BY 1

根据您实际想要实现的目标，您可能会对tablefunc模块感兴趣，该模块可用于创建数据透视表。这里有一个示例。

如果您坚持使用从数据中派生的列名，您将需要动态构建查询，使用类似于您所做的plpgsql函数或匿名代码块（DO语句）。

- Erwin Brandstetter

谢谢回复，但税名的值可能是动态的，可以是任何文本名称，我们必须根据它来生成记录。 - Bhavik Ambani

@BhavikAmbani：如果想要更通用的解决方案，请查看tablefunc模块中的crosstab函数：http://www.postgresql.org/docs/current/static/tablefunc.html - user330315

我正在使用旧版本的Postgresql 8.0.1，它没有交叉表功能，还有一件事，我想使这个函数或过程更加通用，不仅依赖于特定的数据库。 - Bhavik Ambani

如果我想要将一组记录作为函数的结果返回怎么办？ - Bhavik Ambani

@BhavikAmbani：相比较于更近期的版本，Pg 8.0 的功能相对有限。建议先阅读这里的手册，注意查找 SETOF。如有需要，请发布一个新问题。 - Erwin Brandstetter

我们拥有自己的产品已经有10年了，无法更改我们的数据库。请提供替代方案，并使其更具全球性，而不是特定于PostgreSQL。 - Bhavik Ambani

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

您的解决方案是可行的。我为了简化/提高性能/易读性/安全性而大量重写了您的plpgsql函数。

CREATE OR REPLACE FUNCTION f_taxamount()
 RETURNS void AS
$BODY$
DECLARE
    rec record;
BEGIN

    DROP TABLE IF EXISTS tmptable;

    EXECUTE 'CREATE TABLE tmptable (invoiceid integer PRIMARY KEY, '
        || (
           SELECT string_agg(col || ' numeric(9,2) DEFAULT 0', ', ')
           FROM  (
              SELECT quote_ident(lower(replace(taxname,' ','_'))) AS col
              FROM   tbltaxamount
              GROUP  BY 1
              ORDER  BY 1
              ) x
           )
        || ')';

    EXECUTE '
        INSERT INTO tmptable (invoiceid)
        SELECT DISTINCT invoiceid FROM tbltaxamount';

    FOR rec IN
        SELECT taxname, taxamt, invoiceid FROM tbltaxamount ORDER BY invoiceid
    LOOP
        EXECUTE '
            UPDATE tmptable
            SET ' || quote_ident(lower(replace(rec.taxname,' ','_')))
                  || ' = '|| rec.taxamt || ' 
            WHERE invoiceid = ' || rec.invoiceid;
    END LOOP;

END;
$BODY$ LANGUAGE plpgsql;

这适用于PostgreSQL 9.1或更高版本。

对于pg 8.4或更高版本，请替换

SELECT string_agg(col || ' numeric(9,2) DEFAULT 0', ', ')

使用：

SELECT array_to_string(array_agg(col || ' numeric(9,2) DEFAULT 0'), ', ')

对于版本更旧的情况，请创建如下的聚合函数：

CREATE OR REPLACE FUNCTION f_concat_comma(text, text)
  RETURNS text AS
$BODY$
BEGIN
RETURN ($1 || ', '::text) || $2;
END;
$BODY$
  LANGUAGE plpgsql IMMUTABLE;

CREATE AGGREGATE concat_comma(text) (
  SFUNC=f_concat_comma,
  STYPE=text
);

然后写下：

SELECT concat_comma(col || ' numeric(9,2) DEFAULT 0')

此外：

DROP TABLE IF EXISTS tmptable;

“IF EXISTS”子句是在8.2版本中引入的。
如果您使用的版本甚至比这个还要旧，您可以：

IF EXISTS (
    SELECT *
    FROM   pg_catalog.pg_class
    WHERE  oid = 'tmptable'::regclass
    AND    relkind = 'r')
THEN
    DROP TABLE tmptable;
END IF;
*/

升级！

请查看PostgreSQL项目的版本策略。8.0.1版本存在很多漏洞，我强烈建议您进行升级。如果您无法升级到新的主要版本，请至少升级到最新的安全点发布版本，对于您的情况是8.0.26。这可以在原地完成，不需要更改任何其他内容。