Re: [SQL] md5 checksum of a previous row
От | Achilleas Mantzios |
---|---|
Тема | Re: [SQL] md5 checksum of a previous row |
Дата | |
Msg-id | 6a141594-2809-91db-5d2c-032ffa8f7f56@matrix.gatewaynet.com обсуждение исходный текст |
Ответ на | Re: [SQL] md5 checksum of a previous row (Iaam Onkara <iamonkara@gmail.com>) |
Список | pgsql-sql |
On 13/11/2017 16:11, Iaam Onkara wrote:
It will be a FOR EACH ROW trigger, you'll have to write inside smth like :@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?
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> 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.OnkaraThanks,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 exampleAm 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, MartinAm 13.11.2017 um 07:15 schrieb Iaam Onkara:before_insert: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 row3. using on-update or on-delete trigger raise error to prevent update/delete of any row.OnkaraHere 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,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-- Widdersdorfer Str. 415, 50933 Köln; Tel. +49 / 221 / 9544 010 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