在 Postgres 中使用动态查询 + 用户定义的数据类型 [英] Using dynamic query + user defined datatype in Postgres

问题描述

我需要一个函数来规范化我的输入表 features 值.我的 features 表有 9 列，其中 x1,x2...x6 是我需要缩放的输入列.

我可以通过使用静态查询来做到这一点:

创建或替换函数 scale_function()返回 void 作为 $$声明 tav1 特征%rowtype;rang1 特征%rowtype;开始选择 avg(n),avg(x0),avg(x1),avg(x2),avg(x3),avg(x4),avg(x5),avg(x6),avg(y)从特征进入 tav1；选择 max(n)-min(n),max(x0)-min(x0),max(x1)-min(x1),max(x2)-min(x2),max(x3)-min(x3),最大值(x4)-最小值(x4),最大值(x5)-最小值(x5),最大值(x6)-最小值(x6),最大值(y)-最小值(y)从特征进入 rang1；更新功能设置 x1= (x1-tav1.x1)/(rang1.x1),x2= (x2-tav1.x2)/(rang1.x2),x3= (x3-tav1.x3)/(rang1.x3),x4= (x4-tav1.x4)/(rang1.x4),x5= (x5-tav1.x5)/(rang1.x5),x6= (x6-tav1.x6)/(rang1.x6),y= (y-tav1.y)/(rang1.y);返回;结尾;$$ 语言 plpgsql;

但现在我需要一个动态查询来缩放 n 列值，即 x1,x2...,xn(比如说我有 200 多列)功能表.我正在尝试这段代码，但这不起作用，因为用户定义的数据类型存在问题:

创建或替换函数 scale_function(n int)返回 void 作为 $$宣布tav1 特征%rowtype;rang1 特征%rowtype;查询1文本:=''；查询2文本:=''；开始对于我在 0..n环形查询1 := 查询1 ||',avg(x'||i||')';query2 := query2||',max(x'||i||')-min(x'||i||')';结束循环；query1 := 'select avg(n)'||query1||',avg(y) into tav1 from features;';执行查询1；query2 := '从特征中选择 max(n)-min(n)'||query2||',max(y)-min(y) 进入 rang1;';执行查询2；更新功能设置 x1= (x1-tav1.x1)/(rang1.x1), ... ,xn=(xn-tav1.xn)/(rang1.xn),y= (y-tav1.y)/(rang1.y);返回;结尾;$$ 语言 plpgsql;

在这里，我试图将列的 avg() 值转换为用户定义的行类型 tav1 并且必须使用该 tav1 要更新的值.

谁能帮助我如何使用动态查询n"个这样的列来更新 features 表值?

<块引用>

************ 错误 ************错误:列avg"指定了多次SQL 状态:42701上下文:SQL语句select avg(n),avg(x0),avg(x1),avg(x2),avg(x3),avg(x4),avg(x5),avg(x6),avg(y)从特征进入 tav1；"PL/pgSQL 函数 scale_function(integer) 第 12 行 EXECUTE 语句

我使用的是 PostgreSQL 9.3.0.

解决方案

基本更新

用这个更短、更高效的单个 UPDATE 命令替换第一个查询:

更新功能设置(x1，x2，x3，x4，x5，x6，y)= ((x1 - g.avg1)/g.range1, (x2 - g.avg2)/g.range2-- , (x3 - ..., (y - g.avgy)/g.rangey)从 (选择 avg(x1) AS avg1, max(x1) - min(x1) AS range1, avg(x2) AS avg2, max(x2) - min(x2) AS range2-- , 平均 (x3) ..., avg(y) AS avgy, max(y) - min(y) AS rangey从特征) G;

关于短UPDATE语法:

• SQL 更新字段一个表的字段来自另一个表的字段

动态函数

在更简单的查询的基础上，这是一个适用于任意数量列的动态函数:

创建或替换函数 scale_function_dyn()返回无效$功能$宣布列文本；-- 目标列列表验证文本；-- 要插入的值列表聚合文本；-- 聚合查询的列列表开始选择进入 cols、vals、aggsstring_agg(quote_ident(attname), ', '), string_agg(format('(%I - g.%I)/g.%I', attname, 'avg_' ||attname, '范围_' ||attname), ', '), string_agg(format('avg(%1$I) AS %2$I, max(%1$I) - min(%1$I) AS %3$I', attname, 'avg_' ||attname, '范围_' ||attname), ', ')FROM pg_attributeWHERE attrelid = 'features'::regclassAND attname NOT IN ('n', 'x0') -- 从更新中排除列AND NOT attisdropped - 没有丢弃(死)列AND attnum >0;-- 没有系统列EXECUTE format('更新特征设置 (%s) = (%s)FROM (SELECT %s FROM features) g', cols, vals, aggs);结尾$func$ 语言 plpgsql;

更多解释的相关答案:

• 更新触发器函数中的多个列在plpgsql中

SQL Fiddle.

I need a function to normalize my input table features values. My features table has 9 columns out of which x1,x2...x6 are the input columns I need to scale.

I'm able to do it by using a static query:

create or replace function scale_function()
returns void as $$
declare tav1 features%rowtype; rang1 features%rowtype; 

begin   
    select avg(n),avg(x0),avg(x1),avg(x2),avg(x3),avg(x4),avg(x5),avg(x6),avg(y)
    into tav1 from features;

    select max(n)-min(n),max(x0)-min(x0),max(x1)-min(x1),max(x2)-min(x2),max(x3)-min(x3),
    max(x4)-min(x4),max(x5)-min(x5),max(x6)-min(x6),max(y)-min(y)
    into rang1 from features;

    update features
    set     x1= (x1-tav1.x1)/(rang1.x1),x2= (x2-tav1.x2)/(rang1.x2),
        x3= (x3-tav1.x3)/(rang1.x3),x4= (x4-tav1.x4)/(rang1.x4),
        x5= (x5-tav1.x5)/(rang1.x5),x6= (x6-tav1.x6)/(rang1.x6),
        y= (y-tav1.y)/(rang1.y);

return;
end;
$$ language plpgsql;

But now I require a dynamic query to scale n column values i.e., x1,x2...,xn (say I've 200+ columns) in my features table. I'm trying this code but this won't work as there is an issue with a user defined data type:

create or replace function scale_function(n int)
returns void as $$
declare
   tav1 features%rowtype;
   rang1 features%rowtype;
   query1 text :=''; query2 text :=''; 

begin   
for i in 0..n
    loop
        query1 := query1 ||',avg(x'||i||')';
        query2 := query2||',max(x'||i||')-min(x'||i||')';
        end loop;

    query1 := 'select avg(n)'||query1||',avg(y) into tav1 from features;';
    execute query1;

    query2 := 'select max(n)-min(n)'||query2||',max(y)-min(y) into rang1 from features;';
    execute query2;


update features
set    x1= (x1-tav1.x1)/(rang1.x1), ... ,xn=(xn-tav1.xn)/(rang1.xn)
      ,y= (y-tav1.y)/(rang1.y);

return;
end;
$$ language plpgsql;

Here I'm trying to take the avg() values of the columns into a user-defined rowtype tav1 and have to use that tav1 value to update.

Can any one help me how to update the features table values using dynamic query for 'n' such columns?

************ Error ************

ERROR: column "avg" specified more than once

SQL state: 42701

Context: SQL statement "select avg(n),avg(x0),avg(x1),avg(x2),avg(x3),avg(x4),avg(x5),avg(x6),avg(y) into tav1 from features;"

PL/pgSQL function scale_function(integer) line 12 at EXECUTE statement

I'm using PostgreSQL 9.3.0.

解决方案

Basic UPDATE

Replace the first query with this much shorter and more efficient single UPDATE command:

UPDATE features
SET   (x1,x2,x3,x4,x5,x6, y)   
    = ((x1 - g.avg1) / g.range1
     , (x2 - g.avg2) / g.range2
 --  , (x3 - ...
     , (y  - g.avgy) / g.rangey)
FROM (
   SELECT avg(x1) AS avg1, max(x1) - min(x1) AS range1
        , avg(x2) AS avg2, max(x2) - min(x2) AS range2
     -- , avg(x3) ...
        , avg(y) AS avgy, max(y) - min(y) AS rangey
   FROM   features
   ) g;

About the short UPDATE syntax:

• SQL update fields of one table from fields of another one

Dynamic function

Building on the simpler query, here is a dynamic function for any number of columns:

CREATE OR REPLACE FUNCTION scale_function_dyn()
  RETURNS void AS
$func$
DECLARE
   cols text;  -- list of target columns
   vals text;  -- list of values to insert
   aggs text;  -- column list for aggregate query
BEGIN
   SELECT INTO cols, vals, aggs
          string_agg(quote_ident(attname), ', ')
        , string_agg(format('(%I - g.%I) / g.%I'
                          , attname, 'avg_' || attname, 'range_' || attname), ', ')
        , string_agg(format('avg(%1$I) AS %2$I, max(%1$I) - min(%1$I) AS %3$I'
                          , attname, 'avg_' || attname, 'range_' || attname), ', ')
   FROM   pg_attribute
   WHERE  attrelid = 'features'::regclass
   AND    attname NOT IN ('n', 'x0')  -- exclude columns from update
   AND    NOT attisdropped            -- no dropped (dead) columns
   AND    attnum > 0;                 -- no system columns

   EXECUTE format('UPDATE features
                   SET   (%s) = (%s)
                   FROM  (SELECT %s FROM features) g'
                 , cols, vals, aggs);

END
$func$  LANGUAGE plpgsql;

Related answer with more explanation:

• Update multiple columns in a trigger function in plpgsql

SQL Fiddle.

这篇关于在 Postgres 中使用动态查询 + 用户定义的数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！