在PL/pgSQL中动态执行查询

Question

我已经找到了解决方案（我认为），我将要求在甲骨文和SQL服务器上的问题，但似乎无法将其转化为Postgres解决方案。我正在使用Postgres 9.3.6。

这个想法是能够生成有关表内容的“元数据”以用于分析目的。这只能通过为每列运行查询来完成 （AFAIK），以便找出，比如说......最小值/最大值/计数值等。为了自动执行该过程，最好先由数据库生成查询，然后执行。

使用示例销售数据表，我能够使用以下片段为每列生成一个选择查询，返回min（）值：

SELECT 'SELECT min('||column_name||') as minval_'||column_name||' from salesdata '  
FROM information_schema.columns 
WHERE table_name = 'salesdata'

这样做的好处是，无论列数是多少，数据库都会生成代码。现在我想到了无数的地方来存储这些查询，或者是某种类型的变量，或者是一个表列，目的是执行这些查询。我想到了将生成的查询存储在一个变量中，然后使用< code>EXECUTE(或< code>EXECUTE IMMEDIATE)语句执行它们，这是这里采用的方法(见右窗格)，但是Postgres不允许我在函数之外声明变量，我一直在绞尽脑汁想如何将它们结合在一起，这是否是应该遵循的方向，也许还有更简单的方法。

你有什么指示吗，我目前正在尝试这样的事情，受到另一个问题的启发，但不知道我是否朝着正确的方向前进：

CREATE OR REPLACE FUNCTION foo()
RETURNS void AS
$$
DECLARE
    dyn_sql text; 
BEGIN            
dyn_sql := SELECT 'SELECT min('||column_name||') from salesdata'    
    FROM information_schema.columns 
    WHERE table_name = 'salesdata';
execute dyn_sql
END
$$ LANGUAGE PLPGSQL;    

Answer 1

@欧文布兰德施泰特，非常感谢广泛的回答。pg_stats确实提供了一些东西，但是我真正需要的是各种各样的东西，最小值、最大值、计数、空值计数、平均值等等...因此，必须为每一列运行一堆查询，有些查询使用GROUP BY等。

此外，感谢您强调数据类型的重要性，我有点期待这会在某个时候影响工作，我主要关心的是如何自动化查询生成及其执行，最后一点是我主要关心的。

我已经尝试了您提供的函数(我可能需要开始学习一些plpgsql ),但是在SELECT (t::tbl)中出现错误:

ERROR: type "tbl" does not exist

顺便说一下，什么是(t::abc)符号，在python中这应该是一个列表片段，但在PLPGSQL中可能不是这样

Answer 2

在您自己滚动之前，请查看系统表pg_statistic或视图pg_stats：

该视图只允许访问与用户有权读取的表相对应的< code>pg_statistic行，因此允许对该视图的公共读取访问是安全的。

它可能已经包含您将要计算的一些统计信息。它由 ANALYZE 填充，因此您可以在检查之前对新（或任何）表运行它。

-- ANALYZE tbl;  -- optionally, to init / refresh
SELECT * FROM pg_stats
WHERE tablename = 'tbl'
AND   schemaname = 'public';

您希望返回给定表中每一列的最小值。这不是一项简单的任务，因为函数（如一般的 SQL）需要在创建时知道返回类型 - 或者至少在调用时借助多态数据类型知道返回类型。

这个功能可以自动安全地完成所有工作。适用于任何表，只要每个列都允许使用聚合函数< code>min()。但是您需要了解PL/pgSQL。

CREATE OR REPLACE FUNCTION f_min_of(_tbl anyelement)
  RETURNS SETOF anyelement
  LANGUAGE plpgsql AS
$func$
BEGIN
RETURN QUERY EXECUTE (
   SELECT format('SELECT (t::%2$s).* FROM (SELECT min(%1$s) FROM %2$s) t'
                , string_agg(quote_ident(attname), '), min(' ORDER BY attnum)
                , pg_typeof(_tbl)::text)
   FROM   pg_attribute
   WHERE  attrelid = pg_typeof(_tbl)::text::regclass
   AND    NOT attisdropped  -- no dropped (dead) columns
   AND    attnum > 0        -- no system columns
   );
END
$func$;

打电话(重要！):

SELECT * FROM f_min_of(NULL::tbl);  -- tbl being the table name

数据库

您需要了解这些概念：

• 使用EXECUTE
在plpgsql中动态SQL
• 多态类型
• Postgres中的行类型和表类型
• 如何防御SQL注射
• 聚合函数
• 系统目录

相关答案及详细说明：

< li >作为PostgreSQL函数参数的表名 < li >重构PL/pgSQL函数以返回各种选择查询的输出 < li>Postgres数据类型转换 < li >如何使用动态SQL设置复合变量字段的值 < li >如何检查给定架构中是否存在表 < li >在PostgreSQL中选择具有特定列名的列 < li >生成日期系列-使用日期类型作为输入

我正在利用 Postgres 为每个现有表定义行类型。使用多态类型的概念，我能够创建一个适用于任何表的函数。

但是，与基础列相比，一些聚合函数返回相关但不同的数据类型。例如，min（varchar_column）返回text，它是位兼容的，但数据类型不完全相同。PL/pgSQL函数在这里有一个弱点，它坚持使用与RetURNS子句中声明的完全相同的数据类型。没有尝试强制转换，甚至没有隐式强制转换，更不用说赋值强制转换了。

应该改进。用Postgres 9.3测试。没有用9.4重新测试，但我很确定，这方面没有什么变化。

这就是这个构造作为解决方法的由来:

SELECT (t::tbl).* FROM (SELECT ... FROM tbl) t;

通过将整行显式转换为基础表的行类型，我们强制进行赋值转换，以获得每一列的原始数据类型。

对于某些聚合函数，这可能会失败。sum（）为sum（bigint_column）返回数字以适应溢出基本数据类型的和。转换回bigint可能会失败…