Postgres and hstore

I recently started working with the hstore contrib module in Postgresql.  My impetus was  the desire to create a generic way of redirecting deletes on key tables to archive tables.  I do that now using a rule based mechanism. The problem is it is fairly fragile and prone to breakage when the parent table changes.  I had seen references to hstore and had a vague idea of what it could do. This was enough to make me dig deeper and see if I could use it for the delete process.  This is still a work in progress, though I am pleased with what I found so far, which is sketched out below.
1) The parent table:

p_item_no smallint DEFAULT nextval(('plant1_p_item_no_seq'::text)::regclass) NOT NULL,
common character varying(40) NOT NULL,
genus character varying(20),
species character varying(30),
variety character varying(40),
plant_type character varying(6) NOT NULL,
series character varying(20),
season character varying(9),
ts_insert timestamp(0) without time zone DEFAULT now(),
ts_update timestamp(0) without time zone,
user_update character varying(20),
user_insert character varying(20) DEFAULT "session_user"(),
color character varying(30),
category_type character varying(5) DEFAULT 'plant'::character varying NOT NULL,
category_sub_type character varying(15) DEFAULT 'normal'::character varying NOT NULL,
plant_note text

2) The delete table:

CREATE TABLE plant1_delete (
del_id integer NOT NULL,
record_fld public.hstore,
del_ts timestamp with time zone DEFAULT now(),
del_user character varying

3) The delete function and trigger:

CREATE OR REPLACE FUNCTION utility.archive_record()
RETURNS trigger
LANGUAGE plpgsql
tbl_name text := TG_TABLE_NAME || '_delete' ;
archive_row hstore := hstore(OLD.*);
usr_name text := session_user;
EXECUTE 'INSERT INTO ' ||quote_ident(tbl_name)
||'(record_fld,del_ts,del_user)' ||
' VALUES('||quote_literal(archive_row)||',now(),'

CREATE TRIGGER p1_delete AFTER DELETE ON plant1 FOR EACH ROW EXECUTE PROCEDURE utility.archive_record();

4) The end result:

DELETE from plant1 where p_item_no=578;
The raw record in the delete table:
SELECT * from plant1_delete;
-[ RECORD 1 ]------------------------------------------------------------------------------------------

del_id | 6
record_fld | "color"=>NULL, "genus"=>"selaginella", "common"=>"moss little club", "season"=>NULL, "series"=>"", "species"=>"brownii", "variety"=>"", "p_item_no"=>"578", "ts_insert"="2004-01-01 00:00:00", "ts_update"=>"2005-11-21 10:45:30", "plant_note"=>NULL, "plant_type"=>"herb", "user_insert"=>"aklaver", "user_update"=>"aklaver", "category_type"=>"plant", "category_sub_type"=>"normal"
del_ts | 2012-06-23 16:19:00.193426-07
del_user | aklaver

The hstore field parsed out, record selected using value in hstore:
SELECT (each(record_fld)).* from plant1_delete where record_fld ->'p_item_no'='578';

key | value
color |
genus | selaginella
common | moss little club
season |
series |
species | brownii
variety |
p_item_no | 578
ts_insert | 2004-01-01 00:00:00
ts_update | 2005-11-21 10:45:30
plant_note |
plant_type | herb
user_insert | aklaver
user_update | aklaver
category_type | plant
category_sub_type | normal

I can see this making life a lot easier. Added bonus is that psycopg2(a Python database adapter) supports hstore and will adapt from it to a Python dictionary and back again. That is subject for another blog.