postgresql - 在PL/pgSQL中动态执行查询

我已经在Oracle和SQL Server上找到了我想要的问题的解决方案（我想），但似乎无法将其转化为Postgres的解决方案。我用的是Postgres9.3.6。
这样做的目的是能够生成关于表内容的“元数据”，以便进行分析。这只能通过对每一列运行查询来完成（AFAIK），以便发现，比如。。。最小/最大/计数值等等。为了使过程自动化，最好由数据库生成查询，然后执行。
通过一个示例salesdata表，我可以为每个列生成一个select查询，返回min（）值，使用以下代码片段：

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

其优点是，无论列数多少，数据库都将生成代码。
现在我想到了无数个地方来存储这些查询，或者是某种类型的变量，或者是表列，这样做的目的是让这些查询执行。
我想将生成的查询存储在一个变量中，然后使用EXECUTE（或EXECUTE IMMEDIATE）语句执行它们，这是所采用的here（请参见右窗格）方法，但是Postgres不允许我在函数外部声明变量，我一直在琢磨这将如何结合在一起，甚至是应该遵循的方向，也许有更简单的事情。
你有什么建议吗？我正在尝试类似的方法，灵感来自于这个other question但不知道我是否朝着正确的方向前进：

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;

最佳答案

系统统计
在您自己滚动之前，请查看系统表pg_statistic或视图pg_stats：
此视图只允许访问与
对于表，用户有权读取，因此可以安全地
允许对此视图进行公共读取访问。
它可能已经有了一些你要计算的统计数据。它由ANALYZE填充，因此您可以在检查之前为新表（或任何表）运行它。

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

通用动态plpgsql函数
似乎要返回给定表中每一列的最小值。这不是一个简单的任务，因为函数（通常像SQL）要求在创建时知道返回类型，或者至少在调用时借助多态数据类型
此功能可以自动安全地执行所有操作。适用于任何表，只要每个列允许聚合函数min()。但你需要知道你在plpgsql周围的路。

CREATE OR REPLACE FUNCTION f_min_of(_tbl anyelement)
  RETURNS SETOF anyelement 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$  LANGUAGE plpgsql;

呼叫：

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

SQL Fiddle.
你需要理解这些概念：
plpgsql中的动态SQL。
多态类型。
Postgres中的行类型和表类型。
如何防御SQL注入。
聚合函数。
系统目录。
相关回答及详细说明：
Table name as a PostgreSQL function parameter
Refactor a PL/pgSQL function to return the output of various SELECT queries
Postgres data type cast
How to set value of composite variable field using dynamic SQL
How to check if a table exists in a given schema
Select columns with particular column names in PostgreSQL
Generate series of dates - using date type as input
类型不匹配的特殊困难
我利用PyGRESs定义每个现有表的行类型。使用多态类型的概念，我可以创建一个适用于任何表的函数。
但是，与基础列相比，某些聚合函数返回相关但不同的数据类型。例如，EXECUTE返回min(varchar_column)，它是位兼容的，但不是完全相同的数据类型。plpgsql函数在这里有一个弱点，它坚持使用与text子句中声明的完全相同的数据类型。不尝试强制转换，甚至不隐式强制转换，更不用说赋值强制转换。
这应该得到改善。通过Postgres9.3测试。没有用9.4重新测试，但我很确定，这方面没有任何变化。
这就是这个结构的由来：

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

一开始看起来很扭曲。解决方法是：通过显式地将整行强制转换为基础表的行类型，我们强制赋值强制转换为每列的原始数据类型。
请注意，对于某些聚合函数，这可能会失败。RETURNS返回sum()以适应溢出基本数据类型的和。返回到numeric可能会失败。。。

关于postgresql - 在PL/pgSQL中动态执行查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/29616126/