From: Scott Ribe <scott_ribe@elevated-dev.com>
To: selva kumaran <amselvakumaran@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Doubt On JSON in Postgresql
Date: Tue, 8 Jan 2019 06:39:24 -0700
Message-ID: <0014222E-A6F9-42CE-906E-261E1217B878@elevated-dev.com> (raw)
In-Reply-To: <CAPtz40QJ2LH4p5bbEBSUVBW2sE454NODheKQ9viGmFXYNSnjTA@mail.gmail.com>
References: <CAPtz40QJ2LH4p5bbEBSUVBW2sE454NODheKQ9viGmFXYNSnjTA@mail.gmail.com>
I've done something similar, but I only put into the JSON those fields which changed, using IS DISTINCT FROM...
--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/> On Jan 7, 2019, at 10:22 PM, selva kumaran <amselvakumaran@gmail.com> wrote:
>
> Dears,
>
> 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.
> Thanks,
> Selva
> +601136296272
>
>
>
>
>
> 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();
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: scott_ribe@elevated-dev.com, amselvakumaran@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Doubt On JSON in Postgresql
In-Reply-To: <0014222E-A6F9-42CE-906E-261E1217B878@elevated-dev.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox