我创建了一个数据库触发器,将行数据存储在审计表中。在更新操作期间,该触发器从主表中获取数据并将其插入到历史表中(历史表具有列:日期、操作类型(例如更新/删除)、实际行数据)。但是由于输入数据中的带引号文本,该触发器在某些情况下会失败。
如何在我的触发器中转义带引号的文本?
--My trigger
CREATE OR REPLACE FUNCTION audit.if_modified() RETURNS TRIGGER AS $function$
DECLARE
temp_row RECORD; -- a temporary variable used on updates/deletes
v_sql text;
BEGIN
IF TG_WHEN <> 'AFTER' THEN
RAISE EXCEPTION 'audit.if_modified() may only run as an AFTER trigger';
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] - Trigger func added as trigger for unhandled case: %, %',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: syntax error at or near "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数据并创建正确的插入记录。
请分享您的想法,如何编辑触发器以插入带单引号的文本。
谢谢,