[SQL] md5 checksum of a previous row
| От | Iaam Onkara |
|---|---|
| Тема | [SQL] md5 checksum of a previous row |
| Дата | |
| Msg-id | CAMz9UCYU6Vx6E2mtFnvEoFptdmA48d-hHn3x0E-NWuy8Cby5kA@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: [SQL] md5 checksum of a previous row
Re: [SQL] md5 checksum of a previous row Re: [SQL] md5 checksum of a previous row |
| Список | pgsql-sql |
Hi,
I have a requirement to create an tamper proof chain of records for audit purposes. The pseudo code is as follows1. 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/
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;
http://www.sqlfiddle.com/#!17/
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.
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
В списке pgsql-sql по дате отправления: