PL/Python – Pythonic Trigger Functions for Postgres

Maybe this is a bit off-topic, but I just spent quite some time on this and I need to write it down so I can look it up again later :)

These are instructions for Ubuntu running Postgres 8.4. By default, Postgres ships without PL/Python so we need to get it first:

sudo apt-get install postgresql-plpython-8.4

Next, we need to create the language for our database. I’m using PgAdmin3. From there, I ran:

CREATE PROCEDURAL LANGUAGE 'plpython' HANDLER plpython_call_handler;

This should have been it. Let’s try with a simple function:

CREATE FUNCTION replace_e_to_a(text) RETURNS text AS
'
import re
Text1 = re.sub(''e'', ''a'',args[0])
return Text1
'
LANGUAGE 'plpython';
SELECT replace_e_to_a('Meee');

… should return ‘Maaa’.

Now for the juicy part: Let’s create an INSERT trigger function!

First, let’s have a look at the corresponding table structure. We have two tables “user_data” and “user_keywords”. “user_data” is the table that’s being filled with information from external functions. “user_keywords” has to be kept up-to-date. It is supposed to count the appearance of keywords on a per-user base.

user_data user_keywords
user_id, event_id, keywords user_id, keyword, count
1, 1, 'music,rock' 1, 'music', 2
1, 2, 'music,classic' 1, 'rock', 1
 1, 'classic', 1

First, the keyword list has to be split. Then a row has to be inserted for new keywords (compare insert_plan) and the counter has to be increased for existing keywords (update_plan).

The values that are about to be inserted can be accessed via TD[“new”][“column_name”].

CREATE FUNCTION update_keyword_count() RETURNS trigger AS '
keywords = TD["new"]["keywords"]
user = TD["new"]["user_id"]
insert_plan = plpy.prepare("INSERT INTO user_keywords (keyword, count, user_id) VALUES (1,ドル 2,ドル 3ドル)", ["text", "int", "int"])
update_plan = plpy.prepare("UPDATE user_keywords SET count = 3ドル WHERE user_id = 1ドル AND keyword = 2ドル", ["int", "text", "int"])
for keyword in keywords.split(","):
 select_cnt_rows = plpy.prepare("SELECT count(*) AS cnt FROM user_keywords WHERE user_id = 1ドル AND keyword = 2ドル", ["int", "text"])
 cnt_rows = plpy.execute(select_cnt_rows, [user, keyword])
 select_plan = plpy.prepare("SELECT count AS cnt FROM user_keywords WHERE user_id = 1ドル AND keyword = 2ドル", ["int", "text"])
 results = plpy.execute(select_plan, [user, keyword])
 if cnt_rows[0]["cnt"] == 0:
 rv = plpy.execute(insert_plan, [keyword, 1, user])
 else:
 rv = plpy.execute(update_plan, [user, keyword, results[0]["cnt"]+1])
' LANGUAGE plpython;

Now, we need to wire it up by defining the trigger:

CREATE TRIGGER update_keywords
BEFORE INSERT ON user_data
FORE EACH ROW
EXECUTE PROCEDURE update_keyword_count();

… Wasn’t that bad ;)