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.
\n1) The parent table:<\/p>\n
\nCREATE TABLE plant1 (\np_item_no smallint DEFAULT nextval(('plant1_p_item_no_seq'::text)::regclass) NOT NULL,\ncommon character varying(40) NOT NULL,\ngenus character varying(20),\nspecies character varying(30),\nvariety character varying(40),\nplant_type character varying(6) NOT NULL,\nseries character varying(20),\nseason character varying(9),\nts_insert timestamp(0) without time zone DEFAULT now(),\nts_update timestamp(0) without time zone,\nuser_update character varying(20),\nuser_insert character varying(20) DEFAULT \"session_user\"(),\ncolor character varying(30),\ncategory_type character varying(5) DEFAULT 'plant'::character varying NOT NULL,\ncategory_sub_type character varying(15) DEFAULT 'normal'::character varying NOT NULL,\nplant_note text\n);\n<\/pre>\n2) The delete table:<\/p>\n
\nCREATE TABLE plant1_delete (\ndel_id integer NOT NULL,\nrecord_fld public.hstore,\ndel_ts timestamp with time zone DEFAULT now(),\ndel_user character varying\n);\n<\/pre>\n3) The delete function and trigger:<\/p>\n
\nCREATE OR REPLACE FUNCTION utility.archive_record()\nRETURNS trigger\nLANGUAGE plpgsql\nSECURITY DEFINER\nAS $BODY$\nDECLARE\ntbl_name text := TG_TABLE_NAME || '_delete' ;\narchive_row hstore := hstore(OLD.*);\nusr_name text := session_user;\nBEGIN\nEXECUTE 'INSERT INTO ' ||quote_ident(tbl_name)\n||'(record_fld,del_ts,del_user)' ||\n' VALUES('||quote_literal(archive_row)||',now(),'\n||quote_literal(usr_name)||')';\nRETURN OLD;\nEND;\n$BODY$\n\nCREATE TRIGGER p1_delete AFTER DELETE ON plant1 FOR EACH ROW EXECUTE PROCEDURE utility.archive_record();\n<\/pre>\n4) The end result:<\/p>\n
\nDELETE from plant1 where p_item_no=578;\nDELETE 1\nThe raw record in the delete table:\nSELECT * from plant1_delete;\n-[ RECORD 1 ]------------------------------------------------------------------------------------------\n\ndel_id | 6\nrecord_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\"\ndel_ts | 2012-06-23 16:19:00.193426-07\ndel_user | aklaver\n\nThe hstore field parsed out, record selected using value in hstore:\nSELECT (each(record_fld)).* from plant1_delete where record_fld ->'p_item_no'='578';\n\nkey | value\n-------------------+---------------------\ncolor |\ngenus | selaginella\ncommon | moss little club\nseason |\nseries |\nspecies | brownii\nvariety |\np_item_no | 578\nts_insert | 2004-01-01 00:00:00\nts_update | 2005-11-21 10:45:30\nplant_note |\nplant_type | herb\nuser_insert | aklaver\nuser_update | aklaver\ncategory_type | plant\ncategory_sub_type | normal\n<\/pre>\nI 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.<\/p>\n
<\/p>\n
<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
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 … Continue reading