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