agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedmd5 checksum of a previous row
15+ messages / 4 participants
[nested] [flat]
* md5 checksum of a previous row
@ 2017-11-13 06:15 Iaam Onkara <iamonkara@gmail.com>
0 siblings, 3 replies; 15+ messages in thread
From: Iaam Onkara @ 2017-11-13 06:15 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: md5 checksum of a previous row
@ 2017-11-13 07:25 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Iaam Onkara <iamonkara@gmail.com>
2 siblings, 1 reply; 15+ messages in thread
From: Achilleas Mantzios @ 2017-11-13 07:25 UTC (permalink / raw)
To: pgsql-sql
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 <http://www.sqlfiddle.com/#%2117/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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: md5 checksum of a previous row
@ 2017-11-13 07:30 Iaam Onkara <iamonkara@gmail.com>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 15+ messages in thread
From: Iaam Onkara @ 2017-11-13 07:30 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql
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@matrix.gatewaynet.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
>
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: md5 checksum of a previous row
@ 2017-11-13 07:31 MS (direkt) <martin.stoecker@stb-datenservice.de>
parent: Iaam Onkara <iamonkara@gmail.com>
2 siblings, 1 reply; 15+ messages in thread
From: MS (direkt) @ 2017-11-13 07:31 UTC (permalink / raw)
To: pgsql-sql
Hi,
you can easily join the preceeding row, e.g.
select sub.id, sub.created_at, preceedingid, m2.* from (
select m.id, m.created_at, lag(m.id) over(order by m.created_at) as
preceedingid from test m
order by m.created_at) as sub
left join test m2 on m2.id=sub.preceedingid order by sub.created_at;
Regards, Martin
Am 13.11.2017 um 07:15 schrieb Iaam Onkara:
> 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
> <http://www.sqlfiddle.com/#%2117/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
--
Widdersdorfer Str. 415, 50933 Köln; Tel. +49 / 221 / 9544 010
HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: md5 checksum of a previous row
@ 2017-11-13 07:39 Iaam Onkara <iamonkara@gmail.com>
parent: MS (direkt) <martin.stoecker@stb-datenservice.de>
0 siblings, 1 reply; 15+ messages in thread
From: Iaam Onkara @ 2017-11-13 07:39 UTC (permalink / raw)
To: MS (direkt) <martin.stoecker@stb-datenservice.de>; +Cc: pgsql-sql
Thanks that is very helpful.
Now is there a way to fetch previous record without having to specify the
different column names? Seems to me this should be a first class function
in PostgreSQL, but its not.
On Mon, Nov 13, 2017 at 1:31 AM, MS (direkt) <
martin.stoecker@stb-datenservice.de> wrote:
> Hi,
>
> you can easily join the preceeding row, e.g.
>
> select sub.id, sub.created_at, preceedingid, m2.* from (
> select m.id, m.created_at, lag(m.id) over(order by m.created_at) as
> preceedingid from test m
> order by m.created_at) as sub
> left join test m2 on m2.id=sub.preceedingid order by sub.created_at;
>
> Regards, Martin
>
>
> Am 13.11.2017 um 07:15 schrieb Iaam Onkara:
>
> 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
>
>
> -- Widdersdorfer Str. 415, 50933 Köln <https://maps.google.com/?q=Widdersdorfer+Str.+415,+50933+K%C3%B6ln&entry=gmail&source=g;; Tel. +49 / 221 / 9544 010 <+49%20221%209544010>
> HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer
>
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: md5 checksum of a previous row
@ 2017-11-13 07:42 MS (direkt) <martin.stoecker@stb-datenservice.de>
parent: Iaam Onkara <iamonkara@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: MS (direkt) @ 2017-11-13 07:42 UTC (permalink / raw)
To: Iaam Onkara <iamonkara@gmail.com>; +Cc: pgsql-sql
select m2.* from .... will do the job in my example
Am 13.11.2017 um 08:39 schrieb Iaam Onkara:
> Thanks that is very helpful.
>
> Now is there a way to fetch previous record without having to specify
> the different column names? Seems to me this should be a first class
> function in PostgreSQL, but its not.
>
> On Mon, Nov 13, 2017 at 1:31 AM, MS (direkt)
> <martin.stoecker@stb-datenservice.de
> <mailto:martin.stoecker@stb-datenservice.de>> wrote:
>
> Hi,
>
> you can easily join the preceeding row, e.g.
>
> select sub.id <http://sub.id;, sub.created_at, preceedingid, m2.*
> from (
> select m.id <http://m.id;, m.created_at, lag(m.id <http://m.id;)
> over(order by m.created_at) as preceedingid from test m
> order by m.created_at) as sub
> left join test m2 on m2.id <http://m2.id>=sub.preceedingid order
> by sub.created_at;
>
> Regards, Martin
>
>
> Am 13.11.2017 um 07:15 schrieb Iaam Onkara:
>> 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
>> <http://www.sqlfiddle.com/#%2117/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
>
> --
> Widdersdorfer Str. 415, 50933 Köln
> <https://maps.google.com/?q=Widdersdorfer+Str.+415,+50933+K%C3%B6ln&entry=gmail&source=g;; Tel.+49 / 221 / 9544 010 <tel:+49%20221%209544010>
> HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer
>
>
--
Widdersdorfer Str. 415, 50933 Köln; Tel. +49 / 221 / 9544 010
HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: md5 checksum of a previous row
@ 2017-11-13 07:47 Iaam Onkara <iamonkara@gmail.com>
parent: MS (direkt) <martin.stoecker@stb-datenservice.de>
0 siblings, 1 reply; 15+ messages in thread
From: Iaam Onkara @ 2017-11-13 07:47 UTC (permalink / raw)
To: MS (direkt) <martin.stoecker@stb-datenservice.de>; +Cc: pgsql-sql
you will have to still specify m2.id and m2.created_at but having to hard
code the column names is not ideal as any schema change will require a
change in the query. Hence my comment earlier "Seems to me this should be
a first class function in PostgreSQL, but its not."
Thanks,
Onkara
On Mon, Nov 13, 2017 at 1:42 AM, MS (direkt) <
martin.stoecker@stb-datenservice.de> wrote:
> select m2.* from .... will do the job in my example
>
>
> Am 13.11.2017 um 08:39 schrieb Iaam Onkara:
>
> Thanks that is very helpful.
>
> Now is there a way to fetch previous record without having to specify the
> different column names? Seems to me this should be a first class function
> in PostgreSQL, but its not.
>
> On Mon, Nov 13, 2017 at 1:31 AM, MS (direkt) <martin.stoecker@stb-
> datenservice.de> wrote:
>
>> Hi,
>>
>> you can easily join the preceeding row, e.g.
>>
>> select sub.id, sub.created_at, preceedingid, m2.* from (
>> select m.id, m.created_at, lag(m.id) over(order by m.created_at) as
>> preceedingid from test m
>> order by m.created_at) as sub
>> left join test m2 on m2.id=sub.preceedingid order by sub.created_at;
>>
>> Regards, Martin
>>
>>
>> Am 13.11.2017 um 07:15 schrieb Iaam Onkara:
>>
>> 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
>>
>>
>> -- Widdersdorfer Str. 415, 50933 Köln <https://maps.google.com/?q=Widdersdorfer+Str.+415,+50933+K%C3%B6ln&entry=gmail&source=g;; Tel. +49 / 221 / 9544 010 <+49%20221%209544010>
>> HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer
>>
>>
>
> -- Widdersdorfer Str. 415, 50933 Köln <https://maps.google.com/?q=Widdersdorfer+Str.+415,+50933+K%C3%B6ln&entry=gmail&source=g;; Tel. +49 / 221 / 9544 010 <+49%20221%209544010>
> HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer
>
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: md5 checksum of a previous row
@ 2017-11-13 07:49 Thomas Kellerer <spam_eater@gmx.net>
parent: Iaam Onkara <iamonkara@gmail.com>
2 siblings, 2 replies; 15+ messages in thread
From: Thomas Kellerer @ 2017-11-13 07:49 UTC (permalink / raw)
To: pgsql-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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: md5 checksum of a previous row
@ 2017-11-13 07:56 Iaam Onkara <iamonkara@gmail.com>
parent: Thomas Kellerer <spam_eater@gmx.net>
1 sibling, 0 replies; 15+ messages in thread
From: Iaam Onkara @ 2017-11-13 07:56 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; +Cc: pgsql-sql
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@gmx.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: md5 checksum of a previous row
@ 2017-11-13 07:57 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Iaam Onkara <iamonkara@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Achilleas Mantzios @ 2017-11-13 07:57 UTC (permalink / raw)
To: pgsql-sql
On 13/11/2017 09:47, Iaam Onkara wrote:
> you will have to still specify m2.id <http://m2.id; and m2.created_at but having to hard code the column names is not ideal as any schema change will require a change in the query. Hence my comment
> earlier "Seems to me this should be a first class function in PostgreSQL, but its not."
lag() does not work with record type, only anyelement.
What keeps you from writing a trigger and doing smth like :
select md5(test::text) from test ORDER BY created_at DESC LIMIT 1;
This will do the md5 on the whole row.
You should have an extra col to store that.
>
> Thanks,
> Onkara
>
> On Mon, Nov 13, 2017 at 1:42 AM, MS (direkt) <martin.stoecker@stb-datenservice.de <mailto:martin.stoecker@stb-datenservice.de>> wrote:
>
> select m2.* from .... will do the job in my example
>
>
> Am 13.11.2017 um 08:39 schrieb Iaam Onkara:
>> Thanks that is very helpful.
>>
>> Now is there a way to fetch previous record without having to specify the different column names? Seems to me this should be a first class function in PostgreSQL, but its not.
>>
>> On Mon, Nov 13, 2017 at 1:31 AM, MS (direkt) <martin.stoecker@stb-datenservice.de <mailto:martin.stoecker@stb-datenservice.de>> wrote:
>>
>> Hi,
>>
>> you can easily join the preceeding row, e.g.
>>
>> select sub.id <http://sub.id;, sub.created_at, preceedingid, m2.* from (
>> select m.id <http://m.id;, m.created_at, lag(m.id <http://m.id;) over(order by m.created_at) as preceedingid from test m
>> order by m.created_at) as sub
>> left join test m2 on m2.id <http://m2.id>=sub.preceedingid order by sub.created_at;
>>
>> Regards, Martin
>>
>>
>> Am 13.11.2017 um 07:15 schrieb Iaam Onkara:
>>> 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 <http://www.sqlfiddle.com/#%2117/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
>>
>> --
>> Widdersdorfer Str. 415, 50933 Köln <https://maps.google.com/?q=Widdersdorfer+Str.+415,+50933+K%C3%B6ln&entry=gmail&source=g;; Tel.+49 / 221 / 9544 010 <tel:+49%20221%209544010>
>> HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer
>>
>>
>
> --
> Widdersdorfer Str. 415, 50933 Köln <https://maps.google.com/?q=Widdersdorfer+Str.+415,+50933+K%C3%B6ln&entry=gmail&source=g;; Tel.+49 / 221 / 9544 010 <tel:+49%20221%209544010>
> HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer
>
>
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: md5 checksum of a previous row
@ 2017-11-13 08:01 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Thomas Kellerer <spam_eater@gmx.net>
1 sibling, 0 replies; 15+ messages in thread
From: Achilleas Mantzios @ 2017-11-13 08:01 UTC (permalink / raw)
To: pgsql-sql
+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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: md5 checksum of a previous row
@ 2017-11-13 08:01 Iaam Onkara <iamonkara@gmail.com>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 15+ messages in thread
From: Iaam Onkara @ 2017-11-13 08:01 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql
Yes obviously I will need an extra column to store the checksum of the
previous row. The difficultly I was having was not knowing how to read the
whole of the previous row, which I just learned from Thomas and here is the
updated fiddle http://www.sqlfiddle.com/#!17/69843/20
On Mon, Nov 13, 2017 at 1:57 AM, Achilleas Mantzios <
achill@matrix.gatewaynet.com> wrote:
> On 13/11/2017 09:47, Iaam Onkara wrote:
>
> you will have to still specify m2.id and m2.created_at but having to hard
> code the column names is not ideal as any schema change will require a
> change in the query. Hence my comment earlier "Seems to me this should be
> a first class function in PostgreSQL, but its not."
>
>
> lag() does not work with record type, only anyelement.
> What keeps you from writing a trigger and doing smth like :
> select md5(test::text) from test ORDER BY created_at DESC LIMIT 1;
> This will do the md5 on the whole row.
> You should have an extra col to store that.
>
>
> Thanks,
> Onkara
>
> On Mon, Nov 13, 2017 at 1:42 AM, MS (direkt) <martin.stoecker@stb-
> datenservice.de> wrote:
>
>> select m2.* from .... will do the job in my example
>>
>>
>> Am 13.11.2017 um 08:39 schrieb Iaam Onkara:
>>
>> Thanks that is very helpful.
>>
>> Now is there a way to fetch previous record without having to specify the
>> different column names? Seems to me this should be a first class function
>> in PostgreSQL, but its not.
>>
>> On Mon, Nov 13, 2017 at 1:31 AM, MS (direkt) <
>> martin.stoecker@stb-datenservice.de> wrote:
>>
>>> Hi,
>>>
>>> you can easily join the preceeding row, e.g.
>>>
>>> select sub.id, sub.created_at, preceedingid, m2.* from (
>>> select m.id, m.created_at, lag(m.id) over(order by m.created_at) as
>>> preceedingid from test m
>>> order by m.created_at) as sub
>>> left join test m2 on m2.id=sub.preceedingid order by sub.created_at;
>>>
>>> Regards, Martin
>>>
>>>
>>> Am 13.11.2017 um 07:15 schrieb Iaam Onkara:
>>>
>>> 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
>>>
>>>
>>> -- Widdersdorfer Str. 415, 50933 Köln <https://maps.google.com/?q=Widdersdorfer+Str.+415,+50933+K%C3%B6ln&entry=gmail&source=g;; Tel. +49 / 221 / 9544 010 <+49%20221%209544010>
>>> HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer
>>>
>>>
>>
>> -- Widdersdorfer Str. 415, 50933 Köln <https://maps.google.com/?q=Widdersdorfer+Str.+415,+50933+K%C3%B6ln&entry=gmail&source=g;; Tel. +49 / 221 / 9544 010 <+49%20221%209544010>
>> HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer
>>
>>
>
> --
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
>
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: md5 checksum of a previous row
@ 2017-11-13 08:13 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Iaam Onkara <iamonkara@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Achilleas Mantzios @ 2017-11-13 08:13 UTC (permalink / raw)
To: pgsql-sql
On 13/11/2017 10:01, Iaam Onkara wrote:
> Yes obviously I will need an extra column to store the checksum of the previous row. The difficultly I was having was not knowing how to read the whole of the previous row, which I just learned from
> Thomas and here is the updated fiddle http://www.sqlfiddle.com/#!17/69843/20 <http://www.sqlfiddle.com/#%2117/69843/20;
And since you are disabling deletes, updates, your best bet is to try and write the last committed row's md5 inside the trigger we were talking about in the very first post, in which you won't even
need lag() .
>
> On Mon, Nov 13, 2017 at 1:57 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com <mailto:achill@matrix.gatewaynet.com>> wrote:
>
> On 13/11/2017 09:47, Iaam Onkara wrote:
>> you will have to still specify m2.id <http://m2.id; and m2.created_at but having to hard code the column names is not ideal as any schema change will require a change in the query. Hence my
>> comment earlier "Seems to me this should be a first class function in PostgreSQL, but its not."
>
> lag() does not work with record type, only anyelement.
> What keeps you from writing a trigger and doing smth like :
> select md5(test::text) from test ORDER BY created_at DESC LIMIT 1;
> This will do the md5 on the whole row.
> You should have an extra col to store that.
>
>>
>> Thanks,
>> Onkara
>>
>> On Mon, Nov 13, 2017 at 1:42 AM, MS (direkt) <martin.stoecker@stb-datenservice.de <mailto:martin.stoecker@stb-datenservice.de>> wrote:
>>
>> select m2.* from .... will do the job in my example
>>
>>
>> Am 13.11.2017 um 08:39 schrieb Iaam Onkara:
>>> Thanks that is very helpful.
>>>
>>> Now is there a way to fetch previous record without having to specify the different column names? Seems to me this should be a first class function in PostgreSQL, but its not.
>>>
>>> On Mon, Nov 13, 2017 at 1:31 AM, MS (direkt) <martin.stoecker@stb-datenservice.de <mailto:martin.stoecker@stb-datenservice.de>> wrote:
>>>
>>> Hi,
>>>
>>> you can easily join the preceeding row, e.g.
>>>
>>> select sub.id <http://sub.id;, sub.created_at, preceedingid, m2.* from (
>>> select m.id <http://m.id;, m.created_at, lag(m.id <http://m.id;) over(order by m.created_at) as preceedingid from test m
>>> order by m.created_at) as sub
>>> left join test m2 on m2.id <http://m2.id>=sub.preceedingid order by sub.created_at;
>>>
>>> Regards, Martin
>>>
>>>
>>> Am 13.11.2017 um 07:15 schrieb Iaam Onkara:
>>>> 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 <http://www.sqlfiddle.com/#%2117/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
>>>
>>> --
>>> Widdersdorfer Str. 415, 50933 Köln <https://maps.google.com/?q=Widdersdorfer+Str.+415,+50933+K%C3%B6ln&entry=gmail&source=g;; Tel.+49 / 221 / 9544 010 <tel:+49%20221%209544010>
>>> HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer
>>>
>>>
>>
>> --
>> Widdersdorfer Str. 415, 50933 Köln <https://maps.google.com/?q=Widdersdorfer+Str.+415,+50933+K%C3%B6ln&entry=gmail&source=g;; Tel.+49 / 221 / 9544 010 <tel:+49%20221%209544010>
>> HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer
>>
>>
>
> --
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
>
>
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: md5 checksum of a previous row
@ 2017-11-13 14:11 Iaam Onkara <iamonkara@gmail.com>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 15+ messages in thread
From: Iaam Onkara @ 2017-11-13 14:11 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql
@Achilleas I don't think I can avoid using lag. As inside the before
insert trigger I will need to read the previous row. Or am I
misunderstanding you?
On Nov 13, 2017 2:14 AM, "Achilleas Mantzios" <achill@matrix.gatewaynet.com>
wrote:
> On 13/11/2017 10:01, Iaam Onkara wrote:
>
> Yes obviously I will need an extra column to store the checksum of the
> previous row. The difficultly I was having was not knowing how to read the
> whole of the previous row, which I just learned from Thomas and here is the
> updated fiddle http://www.sqlfiddle.com/#!17/69843/20
>
>
> And since you are disabling deletes, updates, your best bet is to try and
> write the last committed row's md5 inside the trigger we were talking about
> in the very first post, in which you won't even need lag() .
>
>
> On Mon, Nov 13, 2017 at 1:57 AM, Achilleas Mantzios <
> achill@matrix.gatewaynet.com> wrote:
>
>> On 13/11/2017 09:47, Iaam Onkara wrote:
>>
>> you will have to still specify m2.id and m2.created_at but having to
>> hard code the column names is not ideal as any schema change will require a
>> change in the query. Hence my comment earlier "Seems to me this should be
>> a first class function in PostgreSQL, but its not."
>>
>>
>> lag() does not work with record type, only anyelement.
>> What keeps you from writing a trigger and doing smth like :
>> select md5(test::text) from test ORDER BY created_at DESC LIMIT 1;
>> This will do the md5 on the whole row.
>> You should have an extra col to store that.
>>
>>
>> Thanks,
>> Onkara
>>
>> On Mon, Nov 13, 2017 at 1:42 AM, MS (direkt) <
>> martin.stoecker@stb-datenservice.de> wrote:
>>
>>> select m2.* from .... will do the job in my example
>>>
>>>
>>> Am 13.11.2017 um 08:39 schrieb Iaam Onkara:
>>>
>>> Thanks that is very helpful.
>>>
>>> Now is there a way to fetch previous record without having to specify
>>> the different column names? Seems to me this should be a first class
>>> function in PostgreSQL, but its not.
>>>
>>> On Mon, Nov 13, 2017 at 1:31 AM, MS (direkt) <
>>> martin.stoecker@stb-datenservice.de> wrote:
>>>
>>>> Hi,
>>>>
>>>> you can easily join the preceeding row, e.g.
>>>>
>>>> select sub.id, sub.created_at, preceedingid, m2.* from (
>>>> select m.id, m.created_at, lag(m.id) over(order by m.created_at) as
>>>> preceedingid from test m
>>>> order by m.created_at) as sub
>>>> left join test m2 on m2.id=sub.preceedingid order by sub.created_at;
>>>>
>>>> Regards, Martin
>>>>
>>>>
>>>> Am 13.11.2017 um 07:15 schrieb Iaam Onkara:
>>>>
>>>> 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
>>>>
>>>>
>>>> -- Widdersdorfer Str. 415, 50933 Köln <https://maps.google.com/?q=Widdersdorfer+Str.+415,+50933+K%C3%B6ln&entry=gmail&source=g;; Tel. +49 / 221 / 9544 010 <+49%20221%209544010>
>>>> HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer
>>>>
>>>>
>>>
>>> -- Widdersdorfer Str. 415, 50933 Köln <https://maps.google.com/?q=Widdersdorfer+Str.+415,+50933+K%C3%B6ln&entry=gmail&source=g;; Tel. +49 / 221 / 9544 010 <+49%20221%209544010>
>>> HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer
>>>
>>>
>>
>> --
>> Achilleas Mantzios
>> IT DEV Lead
>> IT DEPT
>> Dynacom Tankers Mgmt
>>
>>
>
> --
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
>
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: md5 checksum of a previous row
@ 2017-11-13 14:36 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Iaam Onkara <iamonkara@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: Achilleas Mantzios @ 2017-11-13 14:36 UTC (permalink / raw)
To: pgsql-sql
On 13/11/2017 16:11, Iaam Onkara wrote:
> @Achilleas I don't think I can avoid using lag. As inside the before insert trigger I will need to read the previous row. Or am I misunderstanding you?
It will be a FOR EACH ROW trigger, you'll have to write inside smth like :
select md5(test::text) INTO tmplastmd5 from test ORDER BY created_at DESC LIMIT 1;
NEW.lastmd5 := tmplastmd5;
and you are set.
One thing you gotta make sure is on the monotony of created_at. I'd say make it a UNIQUE CONSTRAINT. (a problem you'd have also with the window function version as well)
>
> On Nov 13, 2017 2:14 AM, "Achilleas Mantzios" <achill@matrix.gatewaynet.com <mailto:achill@matrix.gatewaynet.com>> wrote:
>
> On 13/11/2017 10:01, Iaam Onkara wrote:
>> Yes obviously I will need an extra column to store the checksum of the previous row. The difficultly I was having was not knowing how to read the whole of the previous row, which I just learned
>> from Thomas and here is the updated fiddle http://www.sqlfiddle.com/#!17/69843/20 <http://www.sqlfiddle.com/#%2117/69843/20;
>
> And since you are disabling deletes, updates, your best bet is to try and write the last committed row's md5 inside the trigger we were talking about in the very first post, in which you won't
> even need lag() .
>
>>
>> On Mon, Nov 13, 2017 at 1:57 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com <mailto:achill@matrix.gatewaynet.com>> wrote:
>>
>> On 13/11/2017 09:47, Iaam Onkara wrote:
>>> you will have to still specify m2.id <http://m2.id; and m2.created_at but having to hard code the column names is not ideal as any schema change will require a change in the query. Hence
>>> my comment earlier "Seems to me this should be a first class function in PostgreSQL, but its not."
>>
>> lag() does not work with record type, only anyelement.
>> What keeps you from writing a trigger and doing smth like :
>> select md5(test::text) from test ORDER BY created_at DESC LIMIT 1;
>> This will do the md5 on the whole row.
>> You should have an extra col to store that.
>>
>>>
>>> Thanks,
>>> Onkara
>>>
>>> On Mon, Nov 13, 2017 at 1:42 AM, MS (direkt) <martin.stoecker@stb-datenservice.de <mailto:martin.stoecker@stb-datenservice.de>> wrote:
>>>
>>> select m2.* from .... will do the job in my example
>>>
>>>
>>> Am 13.11.2017 um 08:39 schrieb Iaam Onkara:
>>>> Thanks that is very helpful.
>>>>
>>>> Now is there a way to fetch previous record without having to specify the different column names? Seems to me this should be a first class function in PostgreSQL, but its not.
>>>>
>>>> On Mon, Nov 13, 2017 at 1:31 AM, MS (direkt) <martin.stoecker@stb-datenservice.de <mailto:martin.stoecker@stb-datenservice.de>> wrote:
>>>>
>>>> Hi,
>>>>
>>>> you can easily join the preceeding row, e.g.
>>>>
>>>> select sub.id <http://sub.id;, sub.created_at, preceedingid, m2.* from (
>>>> select m.id <http://m.id;, m.created_at, lag(m.id <http://m.id;) over(order by m.created_at) as preceedingid from test m
>>>> order by m.created_at) as sub
>>>> left join test m2 on m2.id <http://m2.id>=sub.preceedingid order by sub.created_at;
>>>>
>>>> Regards, Martin
>>>>
>>>>
>>>> Am 13.11.2017 um 07:15 schrieb Iaam Onkara:
>>>>> 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 <http://www.sqlfiddle.com/#%2117/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
>>>>
>>>> --
>>>> Widdersdorfer Str. 415, 50933 Köln <https://maps.google.com/?q=Widdersdorfer+Str.+415,+50933+K%C3%B6ln&entry=gmail&source=g;; Tel.+49 / 221 / 9544 010 <tel:+49%20221%209544010>
>>>> HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer
>>>>
>>>>
>>>
>>> --
>>> Widdersdorfer Str. 415, 50933 Köln <https://maps.google.com/?q=Widdersdorfer+Str.+415,+50933+K%C3%B6ln&entry=gmail&source=g;; Tel.+49 / 221 / 9544 010 <tel:+49%20221%209544010>
>>> HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer
>>>
>>>
>>
>> --
>> Achilleas Mantzios
>> IT DEV Lead
>> IT DEPT
>> Dynacom Tankers Mgmt
>>
>>
>
> --
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
>
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2017-11-13 14:36 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-11-13 06:15 md5 checksum of a previous row Iaam Onkara <iamonkara@gmail.com>
2017-11-13 07:25 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2017-11-13 07:30 ` Iaam Onkara <iamonkara@gmail.com>
2017-11-13 07:31 ` MS (direkt) <martin.stoecker@stb-datenservice.de>
2017-11-13 07:39 ` Iaam Onkara <iamonkara@gmail.com>
2017-11-13 07:42 ` MS (direkt) <martin.stoecker@stb-datenservice.de>
2017-11-13 07:47 ` Iaam Onkara <iamonkara@gmail.com>
2017-11-13 07:57 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2017-11-13 08:01 ` Iaam Onkara <iamonkara@gmail.com>
2017-11-13 08:13 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2017-11-13 14:11 ` Iaam Onkara <iamonkara@gmail.com>
2017-11-13 14:36 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2017-11-13 07:49 ` Thomas Kellerer <spam_eater@gmx.net>
2017-11-13 07:56 ` Iaam Onkara <iamonkara@gmail.com>
2017-11-13 08:01 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox