Friday, February 10, 2012

PostgreSQL. Mail merging-Part 2.The plpython function

Let's get this thing done.

Creating an auxiliary view,to get the name of the columns tables and views.
CREATE VIEW columns AS SELECT
  c.relname tab,
  a.attname as col
  FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_attribute a ON a.attrelid = c.relname::regclass   
  WHERE
       n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
  AND c.relkind IN ('v','r')
  AND a.attnum > 0


Creating and populating the table where the "recipients" will be listed.
test=# CREATE TABLE deceased (recipient_name text,name_of_deceased text, his_her text, positive_attribute text,my_signature text);
CREATE TABLE
test=# INSERT INTO deceased values ('Jim Morrison','his',' a great artist'),('Kurt Kobain','his',' a tormented soul'),('John Lenon','his',' a charismatic and charming person'),('Janis Joplin','her',' authentic, a punch of energy ');
INSERT 0 3
UPDATE deceased SET my_signature = 'Rene', recipient_name = 'Mr.X'; 

And after some research and thought, I came up with this:
CREATE OR REPLACE FUNCTION mail_merge(source text,document text,path text) RETURNS void AS 
$$
#source -> the table or view that contains the list
#document -> the template with placeholders matching the field names of source prefixed with colons (:)
#path -> the directory where the generated documents will be created
import re
placeholders = []
doc_text = ""
columns = ""
rv = plpy.execute("SELECT col from columns WHERE tab = %s order by 1" % (plpy.quote_literal(source)))
for x in rv:
  placeholders = placeholders + [x['col']] 
rv = plpy.execute("SELECT document from documents WHERE doc_name =  %s " % (plpy.quote_literal(document)))
for x in rv:
  doc_text = doc_text + x['document']
for i in range(0,len(placeholders)):
  columns = columns + placeholders[i]+" "
  if i < len(placeholders) -1:
    columns = columns +","
rv = plpy.execute("SELECT "+columns+" FROM "+source )
for x in rv: 
  tmp = doc_text
  for i in range(0,len(placeholders)):
    tmp = re.sub(":"+str(placeholders[i]),str(x[placeholders[i]]),tmp)
  #here you create the file containing tmp, in path 
$$ LANGUAGE plpython2u;

With this function you can, based on a table or a view create n documents (where n equals the number of rows in the table or view), based on a template, setup as described in my previous entry. The field names of the table or view will determine the placeholders names.

The more I work with python, the more I like it =-).

No comments:

Post a Comment