Postgresql的通用字符串修剪触发器 [英] Generic string trimming trigger for Postgresql

问题描述

问题:

我工作的公司的所有者之一可以直接访问数据库.他在Windows笔记本上使用Navicat.显然，它具有他喜欢从Excel导入数据的位置的功能.问题在于文本字段通常(或可能总是)以\ r \ n结尾.这可能会导致显示，报告和过滤问题.我被要求清理并阻止他这样做.

One of the owners of the company that I work for has direct database access. He uses Navicat on a windows notebook. Apparently, it has a feature that he likes where he can import data from Excel. The problem is that text fields often (or maybe always) end up with a \r\n at the end of them. Which can lead to display, reporting and filtering issues. I've been asked to clean this up and to stop him from doing it.

我知道我可以为每个表添加一个触发器，该触发器将执行以下操作:

I know I can just add a trigger to each table that will do something like:

NEW.customer_name := regexp_replace(NEW.customer_name, '\r\n', '', 'g');

但是，我不想为他可以访问的每个表(超过100个)编写一个单独的触发函数.我的想法是只编写一个泛型函数，然后通过 TG_ARGV [] 参数传入我要更正的列名称数组.

However, I would prefer to not write a separate trigger function for each table that he has access to (there are over 100). My idea was to just write a generic function and then pass in an array of column names I want corrected via the TG_ARGV[] argument.

有没有一种方法可以基于TG_ARGV数组动态更新触发器 NEW 记录?

Is there a way to update a triggers NEW record dynamically based on the TG_ARGV array?

详细信息:

我在x86_64-pc-linux-gnu上使用PostgreSQL 9.6.6

I'm using PostgreSQL 9.6.6 on x86_64-pc-linux-gnu

推荐答案

没有原生方法可以动态访问plpgsql触发函数中 new 记录的列.我知道的唯一方法是使用 jsonb_populate_record():

There is no native means to dynamically access the columns of the new record in a plpgsql trigger function. The only way I know is to convert the record to jsonb, modify it and convert it back to record using jsonb_populate_record():

create or replace function a_trigger()
returns trigger language plpgsql as $$
declare
    j jsonb = to_jsonb(new);
    arg text;
begin
    foreach arg in array tg_argv loop
        if j->>arg is not null then
            j = j || jsonb_build_object(arg, regexp_replace(j->>arg, e'\r\n', '', 'g'));
        end if;
    end loop;
    new = jsonb_populate_record(new, j);
    return new;
end;
$$;

如果您可以使用plpython，则情况会更简单:

create or replace function a_trigger()
returns trigger language plpython3u as $$
    import re
    new = TD["new"]
    for col in TD["args"]:
        new[col] = re.sub(r"\r\n", "", new[col])
    return "MODIFY"
$$;

这篇关于Postgresql的通用字符串修剪触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！