Re: md5 checksum of a previous row

+1 , cool!

On 13/11/2017 09:49, Thomas Kellerer wrote:
>> But how do I use lag function or something like lag to read the previous record as whole.
> You can reference the whole row by using the table name:
>
> select created_at,
> value,
> value - lag(value, 1, 0.0) over(order by created_at) as delta,
> md5(lag(test::text) over(order by created_at)) as the_row
> FROM test
> ORDER BY created_at;
>
> The table reference "test" returns the whole row, e.g. something like:
>
> (bbf35815-479b-4b1b-83c5-5e248aa0a17f,52.00,,"2017-11-13 08:45:16.17231",B)
>
> that can be cast to text and then you can apply the md5() function on the result.
> It does include the parentheses and the commas, but as it does that for every
> row in a consistent manner, it shouldn't matter.
>
>> 2. insert the computed checksum in the current row
> That can also be done using the above technique, something like:
>
> new.hash := md5(new::text);
>
> Thomas
>
>
>
>
>
>

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt