Postgresql：如何在数据库触发器中转义单引号？

8 浏览
0 Comments

Postgresql：如何在数据库触发器中转义单引号？

我创建了一个数据库触发器，将行数据存储在审计表中。

在更新操作期间，该触发器从主表中获取数据并将其插入到历史表中（历史表包含列：日期、操作类型（如更新/删除）、实际行数据）。

但是在某些情况下，由于输入数据中的带引号的文本，触发器会失败。

请问我该如何在触发器中转义带引号的文本？

--我的触发器
CREATE OR REPLACE FUNCTION audit.if_modified() RETURNS TRIGGER AS $function$
DECLARE
    temp_row RECORD; -- 用于更新/删除的临时变量
    v_sql text;
BEGIN
    IF TG_WHEN <> 'AFTER' THEN
        RAISE EXCEPTION 'audit.if_modified() 只能作为 AFTER 触发器运行';
    END IF;
v_sql = 'select * from ' || TG_TABLE_NAME::regclass || '_history';
execute v_sql into temp_row;
select now() into temp_row.action_tstamp_tx;
temp_row.action = SUBSTRING(TG_OP,1,1);
IF (TG_OP = 'UPDATE' AND TG_LEVEL = 'ROW') THEN
    temp_row.row_data = OLD;
ELSIF (TG_OP = 'DELETE' AND TG_LEVEL = 'ROW') THEN
    temp_row.row_data = OLD;
ELSIF (TG_OP = 'INSERT' AND TG_LEVEL = 'ROW') THEN
    temp_row.row_data = NEW;
ELSE
    RAISE EXCEPTION '[audit.if_modified] - 触发器函数添加为未处理的情况的触发器：%，%',TG_OP, TG_LEVEL;
    RETURN NULL;
END IF;
EXECUTE 'INSERT INTO audit.' || TG_TABLE_NAME::regclass || '_history VALUES (''' || 
temp_row.action_tstamp_tx || ''',''' ||
temp_row.action  || ''',''' ||
temp_row.row_data  || ''')'; 
RETURN NULL;
END;
$function$
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = audit,public,pg_catalog;

这对于正常的用例是有效的，但如果varchar数据中有单引号的文本，则无法将数据加载到历史表中。

ERROR:  语法错误，在 "s" 附近
LINE 1: ...VALUES ('2016-02-22 11:44:43.994295-06','U','(6,Tom's,"2016-02...
                                                               ^
QUERY:  INSERT INTO audit.test_history VALUES ('2016-02-22 11:44:43.994295-06','U','(6,Tom's,"2016-02-22 09:49:32.315543")')
CONTEXT:  PL/pgSQL function if_modified() line 30 at EXECUTE

我是PostgreSQL的新手。我尝试了一些选项，例如

regexp_replace() API

和

SELECT into temp_row.row_data unnest(('{' || trim((temp_row.row_data)::text, '()') || '}')::text[]);

等等，但我无法理解如何遍历ROWTYPE数据并创建正确的插入记录。

请分享您对如何编辑我的触发器以插入带有单引号文本的意见。

谢谢，

0