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) 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["cnt"] == 0: rv = plpy.execute(insert_plan, [keyword, 1, user]) else: rv = plpy.execute(update_plan, [user, keyword, results["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 ;)
Thanks for this page.
It was quite hard to assemble enough information about plpython on the interweb for me to write some successful triggers. This proved one of the few helpful sites (the only example code I found with the use of TD[“new”]).
Happy to hear that!
:) good to know if I can do everything about pl in postgres with python :D much better :D