For those who aren't familiar with hstore, it's a key/value storage type that is packaged as an extension or contrib in PostgreSQL 8.2+. In PostgreSQL 9.0 it got a little extra loving in several ways one of which was the introduction of the hstore(record) casting function that converts a record to an hstore. In this article, I'll demonstrate how you can use this new casting function to do very sleek mail merges right in the database. The only caveat is that it seems to only correctly name the keys if it is fed a real table or view. Derived queries such as aggregates etc get keys named f1, f2, etc.
If you are on PostgreSQL 9.1 or above installing -- hstore is just a CREATE EXTENSION hstore; sql command away. If you are on a lower version of PostgreSQL,
you can usually find the hstore.sql in share/contribs.
Records are narly things because they are hard to inspect and shuffle around in the database. The main reason is that its hard to get at the columns without knowing the column names before hand. In order to create a generic merging function that will take say a template and macro-replace any named column with the value, it would be really nice to have a simple key-value format instead of a row column format. Here is where hstore comes in handy.
We have a table that looks something like this:
CREATE TABLE clients(client_id varchar(5) PRIMARY KEY, contact_name varchar(100), business varchar(100));
INSERT INTO clients(client_id, contact_name, business)
VALUES ('DCH', 'John Dewey', 'Dewey, Cheetham, and Howe'),
('IYS', 'Spencer Tracey', 'Investigators at Your Service'),
('ABC', 'Abby Back', 'It is all about ABC');
To convert our records to hstore key-val pairs we do this:
SELECT c.client_id, hstore(c) As mydata
FROM clients As c;
-- output --
client_id | mydata
----------+---------------------------------------------------------------------------------------------------
DCH | "business"=>"Dewey, Cheetham, and Howe", "client_id"=>"DCH", "contact_name"=>"John Dewey"
IYS | "business"=>"Investigators at Your Service", "client_id"=>"IYS", "contact_name"=>"Spencer Tracey"
ABC | "business"=>"It is all about ABC", "client_id"=>"ABC", "contact_name"=>"Abby Back"
To help with mail merging, I wrote this little helper function:
CREATE OR REPLACE FUNCTION merge_replace(param_template text, param_kval hstore
, param_beg_encap text DEFAULT '$', param_end_encap text DEFAULT '$' )
RETURNS text AS
$$
DECLARE var_result text := param_template; r record;
BEGIN
FOR r IN (SELECT (each(param_kval)).* ) LOOP
var_result := replace(var_result, param_beg_encap || r.key || param_end_encap, COALESCE(r.value,''));
END LOOP;
RETURN var_result ;
END
$$
language 'plpgsql';
Now we write the query
SELECT c.client_id
, merge_replace(template.letter,hstore(c)) As personalized_note
FROM clients As c
CROSS JOIN (SELECT 'Dear $contact_name$ of $business$,
You are cordially invited to our party.'::text AS letter) As template;
Which outputs this:
client_id | personalized_note
----------+-------------------------------------------------------
DCH | Dear John Dewey of Dewey, Cheetham, and Howe,
| You are cordially invited to our party.
IYS | Dear Spencer Tracey of Investigators at Your Service,
| You are cordially invited to our party.
ABC | Dear Abby Back of It is all about ABC,
| You are cordially invited to our party.
Now as I mentioned before, if you try to create a subquery and convert the subquery row to an hstore, your keys become pretty useless f1,f2,f3, etc, but luckily we can merge hstores. For this exercise, lets suppose we've got some items we REALLY REALLY want, like a little girl wants a pony for christmas. So we want each invited guest to bring us a present, but we don't want any guest to bring the same present because who needs 2 ponies, so we want to suggest a different present to each client.
Our gift table looks like this:
CREATE TABLE gifts(gift_name varchar(100) primary key)
INSERT INTO gifts(gift_name)
VALUES ('A real live fancy mouse')
, ('Android Phone controlled helicopter with camera')
, ('Train set'), ('Mouse scarf');
We can merge our clients row with a particular gift row by writing a query like below which uses the hstore || operator to merge 2 hstores:
SELECT c.client_id, my_client_data || g.gift_idea AS mydata
FROM (SELECT client_id, hstore(clients) As my_client_data, ROW_NUMBER()OVER() As gift_num
FROM clients) As c INNER JOIN
(SELECT hstore(gifts) As gift_idea, ROW_NUMBER() OVER() As gift_num
FROM gifts) As g
ON (c.gift_num = g.gift_num);
-- outputs --
client_id | mydata
-----------+-------------------------------------------------------------------------------------------------------------------------------------------
DCH | "business"=>"Dewey, Cheetham, and Howe", "client_id"=>"DCH", "gift_name"=>"A real live fancy mouse", "contact_name"=>"John Dewey"
IYS | "business"=>"Investigators at Your Service", "client_id"=>"IYS", "gift_name"=>"Android Phone controlled helicopter with camera"
, "contact_name"=>"Spencer Tracey"
ABC | "business"=>"It is all about ABC", "client_id"=>"ABC", "gift_name"=>"Train set", "contact_name"=>"Abby Back"
And now for the finale -- the real purpose of this tutorial -- how to get gifts you really want:
SELECT c.client_id, merge_replace(template.letter, my_client_data || g.gift_idea ) As personalized_note
FROM (SELECT client_id, hstore(clients) As my_client_data, ROW_NUMBER()OVER() As gift_num
FROM clients) As c INNER JOIN
(SELECT hstore(gifts) As gift_idea, ROW_NUMBER() OVER() As gift_num
FROM gifts) As g
ON (c.gift_num = g.gift_num)
CROSS JOIN (SELECT 'Dear $contact_name$ of $business$,
You are cordially invited to our party. We understand your time is very valuable and that you would feel guilty if you
came to our party empty-handed. Out of the kindness of our heart, we have decided to relieve your burden by
providing you with a gift recommendation from our wish list.
Suggested gift offering: $gift_name$'::text AS letter) As template;
-- output --
client_id | personalized_note
-----------+------------------------------------------------------------------------------------------------------------------------
DCH | Dear John Dewey of Dewey, Cheetham, and Howe,
| You are cordially invited to our party. We understand your time is very valuable and that you would feel guilty if you
| came to our party empty-handed. Out of the kindness of our heart, we have decided to relieve your burden by
| providing you with a gift recommendation from our wish list.
| Suggested gift offering: A real live fancy mouse
IYS | Dear Spencer Tracey of Investigators at Your Service,
| You are cordially invited to our party. We understand your time is very valuable and that you would feel guilty if you
| came to our party empty-handed. Out of the kindness of our heart, we have decided to relieve your burden by
| providing you with a gift recommendation from our wish list.
| Suggested gift offering: Android Phone controlled helicopter with camera
ABC | Dear Abby Back of It is all about ABC,
| You are cordially invited to our party. We understand your time is very valuable and that you would feel guilty if you
| came to our party empty-handed. Out of the kindness of our heart, we have decided to relieve your burden by
| providing you with a gift recommendation from our wish list.
| Suggested gift offering: Train set