从PostgreSQL中的函数创建动态表 [英] Create dynamic table from function in PostgreSQL

问题描述

我有表数据，

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的任何函数获得如下的结果？

Now, how can I get the result as below using any function of PostgreSQL?

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

推荐答案

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;

这篇关于从PostgreSQL中的函数创建动态表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！