在PL/pgSQL中动态执行查询 [英] Executing queries dynamically in PL/pgSQL

问题描述

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

I have found solutions (I think) to the problem I'm about to ask for on Oracle and SQL Server, but can't seem to translate this into a Postgres solution. I am using Postgres 9.3.6.

这个想法是为了能够进行概要分析而能够生成有关表内容的元数据".这只能通过让每一列都运行查询来找出(例如...)最小/最大/计数值等来完成(AFAIK).为了使过程自动化，最好由数据库生成查询，然后执行.

The idea is to be able to generate "metadata" about the table content for profiling purposes. This can only be done (AFAIK) by having queries run for each column so as to find out, say... min/max/count values and such. In order to automate the procedure, it is preferable to have the queries generated by the DB, then executed.

使用示例salesdata表，我可以使用以下代码段为每列生成一个选择查询，并返回min()值:

With an example salesdata table, I'm able to generate a select query for each column, returning the min() value, using the following snippet:

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

优点是，无论列数如何，数据库都会生成代码. 现在，我想到了无数个地方来存储这些查询，这些查询可以是某种变量，也可以是表列，其想法是执行这些查询. 我想将生成的查询存储在变量中，然后使用EXECUTE(或EXECUTE IMMEDIATE)语句执行它们，这是此处(请参见右窗格)，但是Postgres不会让我在函数外部声明变量，而我一直在摸索如何将其组合在一起，即使遵循的方向，也许有一些更简单的方法.

The advantage being that the db will generate the code regardless of the number of columns. Now there's a myriad places I had in mind for storing these queries, either a variable of some sort, or a table column, the idea being to then have these queries execute. I thought of storing the generated queries in a variable then executing them using the EXECUTE (or EXECUTE IMMEDIATE) statement which is the approach employed here (see right pane), but Postgres won't let me declare a variable outside a function and I've been scratching my head with how this would fit together, whether that's even the direction to follow, perhaps there's something simpler.

您有任何建议吗，我目前正在尝试类似的方法，它受

Would you have any pointers, I'm currently trying something like this, inspired by this other question but have no idea whether I'm headed in the right direction:

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 :

System statistics

Before you roll your own, have a look at the system table pg_statistic or the view pg_stats:

此视图仅允许访问对应的pg_statistic行 到用户有权读取的表，因此可以安全地进行 允许公众对此视图进行读取访问.

This view allows access only to rows of pg_statistic that correspond to tables the user has permission to read, and therefore it is safe to allow public read access to this view.

它可能已经具有您要计算的某些统计信息.它由ANALYZE填充，因此您可以在检查之前将其用于新(或任何)表.

It might already have some of the statistics you are about to compute. It's populated by ANALYZE, so you might run that for new (or any) tables before checking.

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

通用动态plpgsql函数

您要返回给定表中每列的最小值.这不是一件容易的事，因为一个函数(通常像SQL一样)要求在创建时或在调用时至少在多态数据类型的帮助下知道返回类型.

Generic dynamic plpgsql function

You want to return the minimum value for every column in a given table. This is not a trivial task, because a function (like SQL in general) demands to know the return type at creation time - or at least at call time with the help of polymorphic data types.

此功能自动安全地执行所有操作.只要任何表都适用，只要每一列都允许使用聚集函数min().但是您需要了解PL/pgSQL的使用方法.

This function does everything automatically and safely. Works for any table, as long as the aggregate function min() is allowed for every column. But you need to know your way around 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

db<>小提琴此处
旧的 sqlfiddle

db<>fiddle here
Old sqlfiddle

您需要了解以下概念:

• 具有EXECUTE
的plpgsql中的动态SQL
• 多态类型
• Postgres中的行类型和表类型
• 如何防范SQL注入
• 集合函数
• 系统目录

• Dynamic SQL in plpgsql with EXECUTE
• Polymorphic types
• Row types and table types in Postgres
• How to defend against SQL injection
• Aggregate functions
• System catalogs

相关答案以及详细说明:

Related answer with detailed explanation:

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

• 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

我利用Postgres为每个现有表定义行类型.使用多态类型的概念，我可以创建一个适用于任何表的 one 函数.

I am taking advantage of Postgres defining a row type for every existing table. Using the concept of polymorphic types I am able to create one function that works for any table.

但是，与基础列相比，某些聚合函数返回相关但不同的数据类型.例如，min(varchar_column)返回text，它是位兼容的，但不完全是相同的数据类型. PL/pgSQL函数在这里有一个弱点，并坚持RETURNS子句中声明的完全数据类型.没有尝试转换，甚至没有隐式转换，更不用说任务转换了.

However, some aggregate functions return related but different data types as compared to the underlying column. For instance, min(varchar_column) returns text, which is bit-compatible, but not exactly the same data type. PL/pgSQL functions have a weak spot here and insist on data types exactly as declared in the RETURNS clause. No attempt to cast, not even implicit casts, not to speak of assignment casts.

应该改进.用Postgres 9.3测试.没用9.4重新测试，但是我很确定，这个区域没有任何变化.

That should be improved. Tested with Postgres 9.3. Did not retest with 9.4, but I am pretty sure, nothing has changed in this area.

这就是该构造作为解决方法出现的地方:

That's where this construct comes in as workaround:

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

通过将整个行显式转换为基础表的行类型，我们强制分配转换以获取每一列的原始数据类型.

By casting the whole row to the row type of the underlying table explicitly we force assignment casts to get original data types for every column.

对于某些聚合函数，这可能会失败. sum()为sum(bigint_column)返回numeric，以容纳溢出基本数据类型的总和.投射回bigint可能会失败...

This might fail for some aggregate function. sum() returns numeric for a sum(bigint_column) to accommodate for a sum overflowing the base data type. Casting back to bigint might fail ...

这篇关于在PL/pgSQL中动态执行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！