Re: md5 checksum of a previous row

The real question is how to get whole previous row not just a column from
the previous row.

Thanks,
Onkara

On Mon, Nov 13, 2017 at 1:25 AM, Achilleas Mantzios <
achill(at)matrix(dot)gatewaynet(dot)com> wrote:

> You just need :
> - to define the way to find the previous row
> - an BEFORE INSERT trigger to compute the checksum, read up some pg/plsql
> or SQL to write your function. To read the row as a whole just use the
> table name without column in the select
> - two rules (update / delete) to do nothing
>
>
> On 13/11/2017 08:15, Iaam Onkara wrote:
>
> Hi,
>
> I have a requirement to create an tamper proof chain of records for audit
> purposes. The pseudo code is as follows
>
> before_insert:
> 1. compute checksum of previous row (or conditionally selected row)
> 2. insert the computed checksum in the current row
> 3. using on-update or on-delete trigger raise error to prevent
> update/delete of any row.
>
> Here are the different options that I have tried using lag and md5
> functions
>
> http://www.sqlfiddle.com/#!17/69843/2
>
> CREATE TABLE test
> ("id" uuid DEFAULT uuid_generate_v4() NOT NULL,
> "value" decimal(5,2) NOT NULL,
> "delta" decimal(5,2),
> "created_at" timestamp default current_timestamp,
> "words" text,
> CONSTRAINT pid PRIMARY KEY (id)
> )
> ;
>
> INSERT INTO test
> (value, words)
> VALUES
> (51.0, 'A'),
> (52.0, 'B'),
> (54.0, 'C'),
> (57.0, 'D')
> ;
>
> select
> created_at, value,
> value - lag(value, 1, 0.0) over(order by created_at) as delta,
> md5(lag(words,1,words) over(order by created_at)) as the_word,
> md5(textin(record_out(test))) as Hash
> FROM test
> ORDER BY created_at;
>
> But how do I use lag function or something like lag to read the previous
> record as whole.
>
> Thanks,
> Onkara
> PS: This was earlier posted in 'pgsql-in-general' mailing list, but I
> think this is a more appropriate list, if I am wrong I am sorry
>
>
> --
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
>
>