BUG #15573: Need more clarification in Json

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: amselvakumaran(at)gmail(dot)com
Subject: BUG #15573: Need more clarification in Json
Date: 2019-01-04 01:30:18
Message-ID: 15573-55170d84c996f77d@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15573
Logged by: selva kumaran
Email address: amselvakumaran(at)gmail(dot)com
PostgreSQL version: 10.5
Operating system: Windows 10
Description:

I have written the below trigger function used for recording changes to
tables into an audit log table.It will record the old and new records, the
table affected, the user who made the change, and a timestamp for each
change in JSON format.

Right now the issue is my business user needs only the particular column
which have changed from old to new not the whole json record.
i.e i need a select query to find the difference between two json columns(
v_old_data and v_new_data ).

Please give me the solution.

CREATE OR REPLACE FUNCTION audit.if_modified_func() RETURNS TRIGGER AS
$body$
DECLARE
v_old_data json;
v_new_data json;
BEGIN
/* If this actually for real auditing (where you need to log EVERY
action),
then you would need to use something like dblink or plperl that
could log outside the transaction,
regardless of whether the transaction committed or rolled back.
*/

/* This dance with casting the NEW and OLD values to a ROW is not
necessary in pg 9.0+ */

IF (TG_OP = 'UPDATE') THEN
v_old_data := ROW_TO_JSON(OLD.*);
v_new_data := ROW_TO_JSON(NEW.*);
INSERT INTO audit.logged_actions
(schema_name,table_name,user_name,action,original_data,new_data,query)
VALUES
(TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG_OP,1,1),v_old_data,v_new_data);
RETURN NEW;
ELSIF (TG_OP = 'DELETE') THEN
v_old_data := ROW_TO_JSON(OLD.*);
INSERT INTO audit.logged_actions
(schema_name,table_name,user_name,action,original_data,query)
VALUES
(TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG_OP,1,1),v_old_data);
RETURN OLD;
ELSIF (TG_OP = 'INSERT') THEN
v_new_data := ROW_TO_JSON(NEW.*);
INSERT INTO audit.logged_actions
(schema_name,table_name,user_name,action,new_data,query)
VALUES
(TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG_OP,1,1),v_new_data);
RETURN NEW;
ELSE
RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - Other action occurred: %,
at %',TG_OP,now();
RETURN NULL;
END IF;

EXCEPTION
WHEN data_exception THEN
RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - UDF ERROR [DATA EXCEPTION]
- SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
RETURN NULL;
WHEN unique_violation THEN
RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - UDF ERROR [UNIQUE] -
SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
RETURN NULL;
WHEN OTHERS THEN
RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - UDF ERROR [OTHER] -
SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
RETURN NULL;
END;
$body$
LANGUAGE plpgsql

CREATE TRIGGER t_if_modified_trg
AFTER INSERT OR UPDATE OR DELETE ON temp_tbl
FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func();

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message David G. Johnston 2019-01-04 04:06:07 Re: BUG #15573: Need more clarification in Json
Previous Message Hugh Ranalli 2019-01-03 21:48:33 Re: BUG #15548: Unaccent does not remove combining diacritical characters