在 PL/pgSQL 中动态执行查询

2023-12-12

我已经找到了我在 Oracle 和 SQL Server 上提出的问题的解决方案（我认为），但似乎无法将其转换为 Postgres 解决方案。我正在使用 Postgres 9.3.6。

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

举个例子salesdata表中，我可以使用以下代码片段为每一列生成一个选择查询，返回 min() 值：

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

优点是无论列数多少，数据库都会生成代码。现在，我想到了无数个地方来存储这些查询，要么是某种变量，要么是表列，其想法是然后执行这些查询。我想将生成的查询存储在一个变量中，然后使用EXECUTE (or EXECUTE IMMEDIATE) 所采用的方法的声明here（参见右窗格），但是 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;

系统统计

在你自己动手之前，先看看系统表pg_statistic或视图pg_stats:

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

它可能已经有一些您要计算的统计数据。它的人口由ANALYZE，因此您可以在检查之前对新（或任何）表运行该命令。

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

通用动态 plpgsql 函数

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

该功能自动且安全地完成所有操作。效劳于any表，只要聚合函数min()每列都允许。但是你need了解 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

数据库小提琴here
Old sqlfiddle

你需要理解这些概念：

plpgsql 中的动态 SQLEXECUTE
多态类型
Postgres 中的行类型和表类型
如何防御SQL注入
聚合函数
系统目录

类型不匹配的特殊困难

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

但是，与基础列相比，某些聚合函数返回相关但不同的数据类型。例如，min(varchar_column)回报text，这是位兼容的，但不是exactly相同的数据类型。 PL/pgSQL 函数在这里有一个弱点，并且坚持数据类型exactly正如声明中所宣布的RETURNS条款。没有尝试强制转换，甚至没有隐式强制转换，更不用说赋值强制转换了。

这一点应该改进。使用 Postgres 9.3 进行测试。没有用 9.4 重新测试，但我很确定，这方面没有任何改变。

这就是这个构造的用处解决方法:

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

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

对于某些聚合函数，这可能会失败。sum()回报numeric for a sum(bigint_column)以适应溢出基本数据类型的总和。投射回bigint可能会失败...

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)