从 PostgreSQL 中的函数创建动态表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8833679/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create dynamic table from function in PostgreSQL
提问by Bhavik Ambani
I have table data,
我有表格数据，
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
Now, how can I get the result as below using any function of PostgreSQL?
现在，如何使用 PostgreSQL 的任何函数获得如下结果？
invoiceid | Service Tax | ABC Tax
----------+-------------+--------
119 | 28.69 | 25.78
回答by Erwin Brandstetter
Your solution is a viable way. I largely rewrote your plpgsql function for simplification / performance / readability / security.
您的解决方案是一种可行的方法。为了简化/性能/可读性/安全性，我在很大程度上重写了您的 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;
This works for PostgreSQL 9.1 or later.
这适用于 PostgreSQL 9.1 或更高版本。
For pg 8.4or later replace
对于 pg 8.4或更高版本，请替换
SELECT string_agg(col || ' numeric(9,2) DEFAULT 0', ', ')
with:
和：
SELECT array_to_string(array_agg(col || ' numeric(9,2) DEFAULT 0'), ', ')
For versions even olderthan that create an aggregate function like this:
对于比那个更旧的版本，创建一个这样的聚合函数：
CREATE OR REPLACE FUNCTION f_concat_comma(text, text)
RETURNS text AS
$BODY$
BEGIN
RETURN ( || ', '::text) || ;
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE;
CREATE AGGREGATE concat_comma(text) (
SFUNC=f_concat_comma,
STYPE=text
);
And then write:
然后写：
SELECT concat_comma(col || ' numeric(9,2) DEFAULT 0')
Also:
还：
DROP TABLE IF EXISTS tmptable;
The clause "IF EXISTS" was introduced with version 8.2.
If you should use a version even olderthan that you should you can:
“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;
*/
Upgrade!
升级！
Have a look at the versioning policy of the PostgreSQL project. Version 8.0.1 is an especially buggy version. I would stronglyadvise you to upgrade. If you can't upgrade to a newer major version, at least upgrade to the latest point-release for security reasons, 8.0.26 in your case. This can be done in place, without changing anything else.
查看PostgreSQL 项目的版本控制策略。8.0.1 版本是一个特别有缺陷的版本。我强烈建议你升级。如果您无法升级到更新的主要版本，出于安全原因，至少升级到最新的 point-release，在您的情况下为 8.0.26。这可以就地完成，无需更改任何其他内容。
回答by Bhavik Ambani
After so may tries I have created below function for creation of the table on the fly and that will display records as above.
在尝试之后，我创建了下面的函数来动态创建表，并且将显示如上的记录。
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;
回答by Erwin Brandstetter
Could look like this:
看起来像这样：
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
Depending on what you actually want to achieve, you might be interested in the tablefunc modulethat can be used to create pivot tables. Here is an example.
根据您实际想要实现的目标，您可能对可用于创建数据透视表的tablefunc 模块感兴趣。这是一个例子。
If you insist on column names derived from data, you will have build your query dynamically, with a plpgsql function like you did or an anonymous code block (DOstatement).
如果您坚持从数据派生的列名，您将使用像您一样的 plpgsql 函数或匿名代码块（DO语句）动态构建查询。
