PL/pgSQL:更新触发器中N列的一般方法? [英] PL/pgSQL: General Way to Update N Columns in Trigger?

问题描述

我正在尝试创建一个将采用常规表并将N列转换为大写的函数.对于这种类型的问题，我还没有运气，但是我能够提出以下建议:

I am attempting create a function that will take a general table and convert N columns to upper case. I haven't had any luck finding a solution to this type of problem, but I was able to come up with the following:

create or replace function uc_on_insert()
returns trigger as
$$
declare
p_tbl varchar = TG_TABLE_NAME;
p_sch varchar = TG_TABLE_SCHEMA;
i varchar;
begin
    for i in 

    (select column_name
    from  INFORMATION_SCHEMA.COLUMNS
    where 1=1
    and table_name ilike p_tbl
    and table_schema ilike p_sch
    and data_type ='character varying')

    loop
           execute 'new.' || i || ' = upper(new.' || i || ');';
       return new;
    end loop;

end;
$$ language plpgsql;

我当前收到此错误:

ERROR:  syntax error at or near "new"
LINE 1: new.c1 = upper(new.c1);
        ^
QUERY:  new.c1 = upper(new.c1);

预期输入将是在任何具有此触发器的表上:

The expected input would be, on any table I have this trigger:

insert into table_one('a', 'b');

>> A, B

，如果我将此触发器放在另一个表上:

and if I put this trigger on another table:

insert into table_two ('a', 3);
>> A, 3

等

推荐答案

这是一个非常棘手的问题.

This is a very tricky question.

您的尝试必然会失败，因为当前行变量NEW在EXECUTE内部不可见.即使是，NEW也是行类型(记录)，而不是数组.与数组元素不同，行的字段不能由数字索引引用.这将导致SQL中的各种问题，因为(与数组相反)每个字段都可以具有不同的数据类型，并且SQL希望知道该数据类型可以事先处理.确实非常棘手.

Your attempt is bound to fail, because the current row variable NEW is not visible inside EXECUTE. And even if it was, NEW is a row type (a record), not an array. Unlike array elements, fields of a row cannot be referenced by numerical index. This would cause all kinds of problems in SQL, because (as opposed to an array) each field can have a different data type, and SQL expects to know the data type to deal with beforehand. Very tricky indeed.

幸运的是，我们之前也处理过类似的问题:

Fortunately, we have dealt with a similar problem before:

• 如何设置复合变量的值动态SQL字段

您会在此处找到足够的说明.
适用于触发功能，并根据列的数据类型，它看起来像这样:

You'll find ample explanation there.
Adapted for a trigger function and depending on the data type of columns, it could look like this:

CREATE OR REPLACE FUNCTION trg_uc_on_insert()
  RETURNS trigger AS
$func$
BEGIN

EXECUTE 'SELECT ' || array_to_string(ARRAY(
      SELECT CASE WHEN atttypid = 'varchar'::regtype
                    -- atttypid = ANY('{text, bpchar, varchar}'::regtype[])
                THEN 'upper(($1).' || quote_ident(attname)
                                   || ')::' || atttypid::regtype::text 
                ELSE '($1).' || quote_ident(attname)
             END AS fld
      FROM   pg_catalog.pg_attribute
      WHERE  attrelid = pg_typeof(NEW)::text::regclass
      AND    attnum > 0
      AND    attisdropped = FALSE
      ORDER  BY attnum
      ), ',')
USING  NEW
INTO   NEW;

RETURN NEW;

END
$func$ LANGUAGE plpgsql;

如果您也想对其他基本字符类型应用相同的规则，请使用带注释的替代项.

If you want to apply the same rule to other basic character types as well, use the commented alternative.

CREATE TRIGGER trg_t_insbef
BEFORE INSERT
ON t              -- works for any table
FOR EACH ROW
EXECUTE PROCEDURE trg_uc_on_insert();

SQL提琴.

只要您仅在表中使用简单类型，并且要大写所有字符数据，还有另一种粗略，快速且简单的方法:将整行强制转换为text ，将大写文本表示形式转换为行类型，​​并用结果更新NEW.有关表格行类型的详细信息

As long as you are using simple types in your tables only and want to upper-case all character data, there is another crude, fast and simple method: Cast the whole row to text, upper-case the text representation, cast back to the row type and update NEW with the result. Details about the row type of a table

• 检查整个表中是否有单个值

触发功能

CREATE OR REPLACE FUNCTION trg_uc_simple_on_insert()
  RETURNS trigger AS
$func$
BEGIN

EXECUTE 'SELECT ($1::' || pg_typeof(NEW) || ').*'
   USING upper(NEW::text)
   INTO NEW;

RETURN NEW;

END
$func$ LANGUAGE plpgsql;

SQL提琴.

我们必须分解行类型，因为SELECT INTO一对一地分配目标行类型的各个字段.我们不能一次分配整行.如果仔细观察，通用"解决方案的效果相同，不太明显.

We have to decompose the row type, because SELECT INTO assigns individual fields of a target row type one-by-one. We cannot assign the whole row at once. If you look closely, the "generic" solution does the same, less obvious.

尽管字符数据在文本表示中区分大小写，但其他基本数字或日期/时间数据类型则不区分大小写.因此，简单的方法可以可靠地工作.可能也适用于大多数其他类型.但是我没有与其他人一起测试，当然也有例外.您必须验证所使用的数据类型.

While character data is case-sensitive in text representation, other basic numeric or date / time data types are not. So the simple method works reliably. Probably with most other types, too. But I did not test with others, and there certainly are exception. You'll have to verify for the data types you use.

此外，尽管代码比通用方法短得多并且更简单，但这不一定会更快，尤其是对于许多未受影响的列.不过，在简单的情况下，它可能要快得多.

Also, while the code is much shorter and simpler than with the generic method, this is not necessarily faster, especially with lots of unaffected columns. It's probably a lot faster in simple cases, though.

这篇关于PL/pgSQL:更新触发器中N列的一般方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！