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