此查询创建您想要的完整 DML 语句:
WITH x AS (
SELECT 'public'::text AS _schema -- provide schema name ..
,'somereport'::text AS _tbl -- .. and table name once
)
SELECT 'SELECT ' || string_agg('sum(' || quote_ident(column_name)
|| ') AS sum_' || quote_ident(column_name), ', ')
|| E'\nFROM ' || quote_ident(x._schema) || '.' || quote_ident(x._tbl)
FROM x, information_schema.columns
WHERE table_schema = _schema
AND table_name = _tbl
AND data_type = 'integer'
GROUP BY x._schema, x._tbl;
您可以单独执行它或将此查询包装在 plpgsql 函数中并使用以下命令自动运行查询EXECUTE
:
全自动化
使用 PostgreSQL 9.1.4 进行测试
CREATE OR REPLACE FUNCTION f_get_sums(_schema text, _tbl text)
RETURNS TABLE(names text[], sums bigint[]) AS
$BODY$
BEGIN
RETURN QUERY EXECUTE (
SELECT 'SELECT ''{'
|| string_agg(quote_ident(c.column_name), ', ' ORDER BY c.column_name)
|| '}''::text[],
ARRAY['
|| string_agg('sum(' || quote_ident(c.column_name) || ')'
, ', ' ORDER BY c.column_name)
|| ']
FROM '
|| quote_ident(_schema) || '.' || quote_ident(_tbl)
FROM information_schema.columns c
WHERE table_schema = _schema
AND table_name = _tbl
AND data_type = 'integer'
);
END;
$BODY$
LANGUAGE plpgsql;
Call:
SELECT unnest(names) AS name, unnest (sums) AS col_sum
FROM f_get_sums('public', 'somereport');
返回:
name | col_sum
---------------+---------
int_col1 | 6614
other_int_col | 8364
third_int_col | 2720642
Explain
困难在于定义RETURN
函数的类型,而返回的列的数量和名称会有所不同。一个有点帮助的细节:你只想要integer
列。
我通过形成一个数组解决了这个问题bigint
(sum(int_col)
回报bigint
)。此外,我还返回一个列名称数组。两者均按列名称的字母顺序排序。
在函数调用中,我将这些数组分开unnest()到达显示的漂亮格式。
动态创建和执行的查询是高级的东西。不要对多层引号感到困惑。基本上你有EXECUTE
它接受一个包含要执行的 SQL 查询的文本参数。该文本又由构建主查询的查询字符串的辅助 SQL 查询提供。
如果一次太多或者plpgsql
对你来说相当新鲜,从这个相关答案我在其中解释了处理更简单功能的基础知识,并提供了主要功能手册的链接。
If 表现必须直接查询 Postgres 目录(pg_catalog.pg_attributes
)而不是使用标准化(但速度慢)information_schema.columns
。这里有一个简单的例子pg_attributes.