agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Thomas Kellerer <spam_eater@gmx.net>
To: pgsql-sql@postgresql.org
Subject: Re: md5 checksum of a previous row
Date: Mon, 13 Nov 2017 08:49:16 +0100
Message-ID: <oubipl$q9i$1@blaine.gmane.org> (raw)
In-Reply-To: <CAMz9UCYU6Vx6E2mtFnvEoFptdmA48d-hHn3x0E-NWuy8Cby5kA@mail.gmail.com>
References: <CAMz9UCYU6Vx6E2mtFnvEoFptdmA48d-hHn3x0E-NWuy8Cby5kA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

> 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

view thread (15+ messages)  latest in thread

Message-ID: <oubipl$q9i$1@blaine.gmane.org>
Permalink:  ../oubipl$q9i$1@blaine.gmane.org/
Also on:    postgresql.org/message-id/oubipl$q9i$1@blaine.gmane.org

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: spam_eater@gmx.net
  Subject: Re: md5 checksum of a previous row
  In-Reply-To: <oubipl$q9i$1@blaine.gmane.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox