如何保存通过触发器传递的字符变化逗号分隔的数据? [英] How to save a data with comma in character varying that passes through a trigger?
问题描述
我的字符类型字段有所不同,但是尝试保存包含小数的数据时出现错误.我想毫无问题地保存数据.
I have a field of type character varying but I get an error when trying to save a data that contains decimal. I want to save that data without problem.
这是我的触发器:
CREATE TRIGGER "public.usuarios_trigger_process_audit"
BEFORE INSERT OR UPDATE OR DELETE
ON usuarios
FOR EACH ROW
EXECUTE PROCEDURE process_audit();
此过程:
This the PROCEDURE:
DECLARE
newtable text;
col information_schema.columns %ROWTYPE;
txtquery text;
line_old TEXT;
tmpquery text;
i int;
columns_old text[];
BEGIN
IF ( TG_TABLE_SCHEMA = 'public' ) THEN
SELECT TG_TABLE_NAME || '_actividad' INTO newtable; /* select TG_RELNAME || '_actividad' into newtable; */
ELSE
SELECT TG_TABLE_SCHEMA || '_' || TG_TABLE_NAME || '_actividad' INTO newtable; /* select TG_RELNAME || '_actividad' into newtable; */
END IF;
PERFORM creartablaactividad( TG_TABLE_SCHEMA, TG_TABLE_NAME );
IF ( TG_OP = 'DELETE' ) THEN
line_old := TRIM( substr(OLD::text,2,(select length(OLD::text)-2)) );
columns_old := STRING_TO_ARRAY( line_old, ',' );
i := 0;
tmpquery := '''' || array_to_string(columns_old, ''',''') || '''';
tmpquery := replace(tmpquery,','''',',',NULL,');
/* SELECT 'INSERT INTO actividad.' || newtable ||' SELECT user, inet_client_addr(), now (), ''D'',' || replace(tmpquery, ',''''',',NULL') into txtquery; */
SELECT 'INSERT INTO actividad.' || newtable ||' SELECT user, now (), ''D'',' || replace(tmpquery, ',''''',',NULL') into txtquery;
EXECUTE txtquery;
RETURN OLD;
ELSIF ( TG_OP = 'UPDATE' ) THEN
line_old := TRIM( substr(OLD::text,2,(select length(OLD::text)-2)) );
columns_old := STRING_TO_ARRAY( line_old, ',' );
i := 0;
tmpquery := '''' || array_to_string(columns_old, ''',''') || '''';
tmpquery := replace(tmpquery,','''',',',NULL,');
tmpquery := replace(tmpquery,','''',',',NULL,');
/* SELECT 'INSERT INTO actividad.' || newtable ||' SELECT user, inet_client_addr(), now (), ''ANT'',' || replace(tmpquery, ',''''',',NULL') into txtquery; */
SELECT 'INSERT INTO actividad.' || newtable ||' SELECT user, now (), ''ANT'',' || replace(tmpquery, ',''''',',NULL') into txtquery;
EXECUTE txtquery;
line_old := TRIM( substr(NEW::text,2,(select length(NEW::text)-2)) );
columns_old := STRING_TO_ARRAY( line_old, ',' );
i := 0;
tmpquery := '''' || array_to_string(columns_old, ''',''') || '''';
tmpquery := replace(tmpquery,','''',',',NULL,');
/* SELECT 'INSERT INTO actividad.' || newtable ||' SELECT user, inet_client_addr(), now (), ''U'',' || replace(tmpquery, ',''''',',NULL') into txtquery; */
SELECT 'INSERT INTO actividad.' || newtable ||' SELECT user, now (), ''U'',' || replace(tmpquery, ',''''',',NULL') into txtquery;
EXECUTE txtquery;
RETURN NEW;
ELSIF ( TG_OP = 'INSERT' ) THEN
line_old := TRIM( substr(NEW::text,2,(select length(NEW::text)-2)) );
columns_old := STRING_TO_ARRAY( line_old, ',' );
i := 0;
tmpquery := '''' || array_to_string(columns_old, ''',''') || '''';
tmpquery := replace(tmpquery,','''',',',NULL,');
/* SELECT 'INSERT INTO actividad.' || newtable ||' SELECT user, inet_client_addr(), now (), ''I'',' || replace(tmpquery, ',''''',',NULL') into txtquery; */
SELECT 'INSERT INTO actividad.' || newtable ||' SELECT user, now (), ''I'',' || replace(tmpquery, ',''''',',NULL') into txtquery;
EXECUTE txtquery;
RETURN NEW;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
我的餐桌使用习惯:
My table usuarios:
错误:
推荐答案
您可以使用format()
简化动态SQL查询的创建过程,因为它将自动正确地处理标识符和文字.人们通常忽略的一件事是,您可以使用(...).*
将单个记录表达式扩展到其所有列-这也适用于触发器中的NEW
和OLD
记录变量,例如select (new).*
You can use format()
to make creating a dynamic SQL query much easier as it will automatically deal with identifiers and literals correctly. One thing that people usually overlook is that you can expand a single record expression to all its columns using (...).*
- this also works for NEW
and OLD
record variables in a trigger, e.g. select (new).*
您还可以使用execute语句的="nofollow noreferrer"> using
关键字.无需在记录和文本表示之间来回转换记录.
You can also pass variables to a dynamic SQL with the using
keyword of the execute
statement. There is no need to convert the record back and forth between a record and a text representation.
利用这种可能性,您的触发功能可以简化为:
Using that possibility your trigger function can be simplified to:
DECLARE
l_sql text;
BEGIN
IF TG_TABLE_SCHEMA = 'public' THEN
newtable := TG_TABLE_NAME || '_actividad';
ELSE
newtable := TG_TABLE_SCHEMA || '_' || TG_TABLE_NAME || '_actividad';
END IF;
PERFORM creartablaactividad(TG_TABLE_SCHEMA, TG_TABLE_NAME);
l_sql := 'INSERT INTO actividad.%I SELECT current_user, current_timestamp, %L, ($1).*';
IF TG_OP = 'DELETE' THEN
execute format(l_sql, newtable, 'D') using OLD;
RETURN OLD;
ELSE
-- covers UPDATE and INSERT
execute format(l_sql, newtable, 'U') using NEW;
RETURN NEW;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
使用像%I
和%L
这样的占位符,也可以只定义一次实际的SQL并重新使用它.那些参数"由format()
函数(保留了$1
)替换
Using placeholders like %I
and %L
also makes it possible to define the actual SQL only once and re-use it. Those "parameters" are replaced by the format()
function (which preserves the $1
)
请注意在SQL字符串中使用($1).*
的情况.这将使execute
语句将记录参数$1
扩展到其所有列.记录本身通过USING
关键字本地"传递.
Note the use of ($1).*
inside the SQL string. That will make the execute
statement expand the record parameter $1
to all its columns. The record itself is passed "natively" with the USING
keyword.
在没有目标列列表(insert into some_table ...
而不是insert into some_table (col1, col2, ...) ...
)的情况下使用INSERT
是一件非常脆弱的事情.如果源和目标不匹配,则插入很容易失败. .
The use of INSERT
without a target column list (insert into some_table ...
instead of insert into some_table (col1, col2, ...) ...
) is a pretty fragile thing to do. If the source and the target don't match the insert can fail quite easily. .
如果您不对审计表进行大量报告(使用显式列名将更为有效),则可能需要考虑使用JSON
或HSTORE
列进行存储的更通用的审计触发器整个记录.有几种现成的审计触发器可用:
If you don't run massive reporting on the audit tables (where having explicit column names would be much more efficient) you might want to think of a more generic audit trigger using a JSON
or HSTORE
column to store the whole record. There are several ready-made audit triggers available:
- http://okbob.blogspot.de /2015/01/most-simply-implementation-of-history.html
- https://github.com/wingspan/wingspan-auditing
- https://www.cybertec-postgresql.com/zh-CN/tracking-changes-in-postgresql/
- https://wiki.postgresql.org/wiki/Audit_trigger_91plus
- http://okbob.blogspot.de/2015/01/most-simply-implementation-of-history.html
- https://github.com/wingspan/wingspan-auditing
- https://www.cybertec-postgresql.com/en/tracking-changes-in-postgresql/
- https://wiki.postgresql.org/wiki/Audit_trigger_91plus
这篇关于如何保存通过触发器传递的字符变化逗号分隔的数据?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!