Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 12.2 \(3445.102.3\))
Subject: Re: Doubt On JSON in Postgresql
From: Scott Ribe <scott_ribe@elevated-dev.com>
In-Reply-To: 
 <CAPtz40QJ2LH4p5bbEBSUVBW2sE454NODheKQ9viGmFXYNSnjTA@mail.gmail.com>
Date: Tue, 8 Jan 2019 06:39:24 -0700
Cc: pgsql-admin@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <0014222E-A6F9-42CE-906E-261E1217B878@elevated-dev.com>
References: 
 <CAPtz40QJ2LH4p5bbEBSUVBW2sE454NODheKQ9viGmFXYNSnjTA@mail.gmail.com>
To: selva kumaran <amselvakumaran@gmail.com>
Precedence: bulk

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:
>=20
> Dears,
>=20
> 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.
>=20
> 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 ).
>=20
> Please give me the solution.
> Thanks,
> Selva
> +601136296272
>=20
>=20
>=20
>=20
>=20
> CREATE OR REPLACE FUNCTION audit.if_modified_func() RETURNS TRIGGER AS
>  $body$
>=20
> DECLARE
>=20
>     v_old_data json
> ;
>=20
>     v_new_data json
> ;
> BEGIN
>=20
>    =20
> /*  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.
>     */
>=20
> =20
>    =20
> /* This dance with casting the NEW and OLD values to a ROW is not =
necessary in pg 9.0+ */
>=20
> =20
>    =20
> IF (TG_OP =3D 'UPDATE') THEN
>=20
>         v_old_data=20
> :=3D ROW_TO_JSON(OLD.*);
>=20
>         v_new_data=20
> :=3D ROW_TO_JSON(NEW.*);
>=20
>        =20
> INSERT INTO audit.logged_actions =
(schema_name,table_name,user_name,action,original_data,new_data,query)
> =20
>        =20
> VALUES =
(TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG=
_OP,1,1),v_old_data,v_new_data);
>=20
>        =20
> RETURN NEW;
>=20
>    =20
> ELSIF (TG_OP =3D 'DELETE') THEN
>=20
>         v_old_data=20
> :=3D ROW_TO_JSON(OLD.*);
>=20
>        =20
> INSERT INTO audit.logged_actions =
(schema_name,table_name,user_name,action,original_data,query)
>=20
>        =20
> VALUES =
(TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG=
_OP,1,1),v_old_data);
>=20
>        =20
> RETURN OLD;
>=20
>    =20
> ELSIF (TG_OP =3D 'INSERT') THEN
>=20
>         v_new_data=20
> :=3D ROW_TO_JSON(NEW.*);
>=20
>        =20
> INSERT INTO audit.logged_actions =
(schema_name,table_name,user_name,action,new_data,query)
>=20
>        =20
> VALUES =
(TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG=
_OP,1,1),v_new_data);
>=20
>        =20
> RETURN NEW;
>=20
>    =20
> ELSE
>=20
>        =20
> RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - Other action occurred: %, at =
%',TG_OP,now();
>=20
>        =20
> RETURN NULL;
>=20
>    =20
> END IF;
>=20
> =20
>=20
> EXCEPTION
>=20
>    =20
> WHEN data_exception THEN
>=20
>        =20
> RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - UDF ERROR [DATA EXCEPTION] - =
SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
>=20
>        =20
> RETURN NULL;
>=20
>    =20
> WHEN unique_violation THEN
>=20
>        =20
> RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - UDF ERROR [UNIQUE] - =
SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
>=20
>        =20
> RETURN NULL;
>=20
>    =20
> WHEN OTHERS THEN
>=20
>        =20
> RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - UDF ERROR [OTHER] - =
SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
>=20
>        =20
> RETURN NULL;
> END;
>=20
> $body$
> LANGUAGE plpgsql
>=20
>=20
>=20
>  CREATE TRIGGER
>  t_if_modified_trg=20
>  AFTER=20
> INSERT OR UPDATE OR DELETE ON
>  temp_tbl
> =20
> FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func();