在Postgres中使用动态查询+用户定义的数据类型

Question

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

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

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;

但是现在我需要一个动态查询来缩放n列值，即x1,x2...,xn (假设我有200+列)在200+表中。我正在尝试这段代码，但由于用户定义的数据类型存在问题，这是行不通的：

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;

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

有谁能帮助我如何使用对'n‘这样的列的动态查询来更新features表值？

*错误*错误:列"avg“指定不止一次SQL状态: 42701上下文: SQL语句”选择avg(n)、avg(x0)、avg(x1)、avg(x2)、avg(x3)、avg(x4)、avg(x5)、avg(x6)、avg(y)从特性转换为tav1；“PL/pgSQL函数scale_function(整数)第12行

我使用的是PostgreSQL 9.3.0。

Answer 1

基本UPDATE

用以下更短、更有效的单个UPDATE命令替换第一个查询：

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;

关于简短的UPDATE语法：

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

动态函数

在简单查询的基础上，这里为任意数量的列提供了一个动态函数：

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;

相关的答案和更多的解释：

• Update multiple columns in a trigger function in plpgsql

SQL Fiddle.