To: pgsql-sql@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Re: md5 checksum of a previous row
Date: Mon, 13 Nov 2017 08:49:16 +0100
Lines: 29
Message-ID: <oubipl$q9i$1@blaine.gmane.org>
References: 
 <CAMz9UCYU6Vx6E2mtFnvEoFptdmA48d-hHn3x0E-NWuy8Cby5kA@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.23)
 Gecko/20090812 Thunderbird/2.0.0.23 Mnenhy/0.7.6.666
In-Reply-To: 
 <CAMz9UCYU6Vx6E2mtFnvEoFptdmA48d-hHn3x0E-NWuy8Cby5kA@mail.gmail.com>
Content-Language: de-DE
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

> 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql