Thursday, November 29, 2012

Can your RDBMS do a convenient audit trail?

Can your RDBMS do this?

create table person
(
 person_id serial not null primary key,
 lastname text not null,
 firstname text not null,
 nickname text null,
 favorite_number int null
);


insert into person(lastname,firstname,nickname, favorite_number) values
('lennon','john winston','john',default),
('mccartney','james paul','paul',default),
('harrison','george',default,default),
('starr','richard','ringo', 10);

select skeys(hstore(p.*)) as field, svals(hstore(p.*)) as value from person p;



Output:

      field      |    value     
-----------------+--------------
 lastname        | lennon
 nickname        | john
 firstname       | john winston
 person_id       | 2
 favorite_number | 
 lastname        | mccartney
 nickname        | paul
 firstname       | james paul
 person_id       | 3
 favorite_number | 
 lastname        | harrison
 nickname        | 
 firstname       | george
 person_id       | 4
 favorite_number | 
 lastname        | starr
 nickname        | ringo
 firstname       | richard
 person_id       | 5
 favorite_number | 10
(20 rows)

hstore can be used as a convenient mechanism for audit trail

http://www.sqlfiddle.com/#!1/d5729/1


No comments:

Post a Comment