I try to do as explained in:
我试着按照以下解释去做:
https://wiki.postgresql.org/wiki/Audit_trigger
https://wiki.postgresql.org/wiki/Audit_trigger
Auditing values as JSON
审计值作为JSON
For PostgreSQL 9.2, or 9.1 with the fantastic json_91 addon, you can log the old and new values in the table as structured json instead of flat text, giving you much more power to query your audit history. Just change the types of v_old_data, v_new_data, original_data and new_data from TEXT to json, then replace ROW(OLD.) and ROW(NEW.) with row_to_json(OLD) and row_to_json(NEW) respectively.
对于PostgreSQL 9.2或9.1,使用出色的json_91 addon,您可以将表中的旧值和新值作为结构化的json而不是纯文本进行日志记录,从而使您能够更强大地查询审计历史。只需将v_old_data、v_new_data、original_data和new_data的类型从文本更改为json,然后将ROW(OLD.)和ROW(NEW.)分别替换为row_to_json(OLD)和row_to_json(NEW)。
However this get me a error:
然而,这给我带来了一个错误:
CREATE OR REPLACE FUNCTION add_log (name text, Action TEXT, data jsonb, OUT RETURNS BOOLEAN)
AS $$
BEGIN
RETURNS = true;
END;
$$
LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION log_city() RETURNS TRIGGER AS
$$
DECLARE
v_new_data jsonb;
BEGIN
IF (TG_OP = 'UPDATE') THEN
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
v_new_data := row_to_jsonb(NEW);
EXECUTE add_log('City', 'City.New', v_new_data);
RETURN NEW;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$$ LANGUAGE plpgsql;
INSERT INTO Location (city, state, country) values ('a', 'b' , 'c')
It say:
它说:
ERROR: function row_to_jsonb(location) does not exist
错误:函数row_to_jsonb(位置)不存在
If I put v_new_data := row_to_jsonb(ROW(NEW));
then I get:
如果我输入v_new_data:= row_to_jsonb(ROW(NEW));然后我得到:
ERROR: function row_to_jsonb(record) does not exist
错误:函数row_to_jsonb(记录)不存在。
1 个解决方案
#1
1
It's stated in the documentation that
文件中说
Table 9-42 shows the functions that are available for creating json and jsonb values. (There are no equivalent functions for jsonb, of the row_to_json and array_to_json functions. However, the to_jsonb function supplies much the same functionality as these functions would.)
表9-42显示了用于创建json和jsonb值的函数。(对于row_to_json和array_to_json函数,jsonb没有等效的函数。但是,to_jsonb函数提供的功能与这些函数的功能相同。
thus it's row_to_json
that has to be used. a row_to_jsonb does not exists but row_to_json
produces the desired result for the JSONB
type as well.
因此必须使用row_to_json。row_to_jsonb不存在,但是row_to_json也为JSONB类型生成所需的结果。
#1
1
It's stated in the documentation that
文件中说
Table 9-42 shows the functions that are available for creating json and jsonb values. (There are no equivalent functions for jsonb, of the row_to_json and array_to_json functions. However, the to_jsonb function supplies much the same functionality as these functions would.)
表9-42显示了用于创建json和jsonb值的函数。(对于row_to_json和array_to_json函数,jsonb没有等效的函数。但是,to_jsonb函数提供的功能与这些函数的功能相同。
thus it's row_to_json
that has to be used. a row_to_jsonb does not exists but row_to_json
produces the desired result for the JSONB
type as well.
因此必须使用row_to_json。row_to_jsonb不存在,但是row_to_json也为JSONB类型生成所需的结果。