Examples

[James William Pye]

On Tue, Feb 14, 2006 at 08:53:08AM -0800, Adrian Klaver wrote:
> Are their examples available on writing functions in the backend. I ask 
> because I am having trouble accessing the args to a function. Also I would 
> not mind seeing a trigger example to figure out how to get at the NEW and OLD 
> tuples.

Hrm, sadly I don't have examples in the documentation[1]. I'll try to work some
in over the weekend.

In the mean time, I'll give you a quick overview.

NEW and OLD are set as attributes on 'self':

CREATE TABLE thistable (id SERIAL, data text);

CREATE FUNCTION update_if_exists_trig() RETURNS TRIGGER LANGUAGE python
AS $$
_check_id = Postgres.CachedQuery('SELECT ($1 IN (SELECT id FROM thistable))')
check_id = lambda x: _check_id(x).next()[0]

if check_id(self.new['id']):
	updater = Postgres.CachedQuery('UPDATE thistable SET data = $2 WHERE id = $1')
	updater(self.new['id'], self.new[1])
	return

return self.new
$$;

CREATE TRIGGER uix ON thistable BEFORE INSERT EXECUTE PROCEDURE update_if_exists_trig;

Simply, 'self.old' is the OLD tuple, and 'self.new' is the NEW tuple.

Function args are even easier:

CREATE FUNCTION foo(int, text) RETURNS text
IMMUTABLE STRICT LANGUAGE python
AS 'return str(args[0]) + str(args[1])';

[1] http://python.projects.postgresql.org/doc/dev/be.html
-- 
Regards, James William Pye