Re: md5 checksum of a previous row

Thank you Thomas that 'test::txt' was the answer I was looking for. :)

Apparently the documentation for lag function
https://www.postgresql.org/docs/9.6/static/functions-window.html wasn't
very informative on the use of 'test::txt' at least for me

Best Regards,
Onkara

On Mon, Nov 13, 2017 at 1:49 AM, Thomas Kellerer <spam_eater(at)gmx(dot)net> 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
>
>
>
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>