Releases
v1.0
v1.0.1
v1.0.0
Documentation
v1.0.1 2011-09-24
Normal Functions
Functions are Modules, so just define main and you're off:
CREATE OR REPLACE FUNCTION
hello(world text) RETURNS text LANGUAGE python AS
$$
import Postgres

template = "Hello, {0}!"

@pytypes
def main(*args):
    tail = args[0]
    if tail is None:
        tail = 'World'
    return template.format(tail)
$$;

SELECT hello(NULL);
--     hello     
-----------------
-- Hello, World!
--(1 row)

SELECT hello('Bunnies');
--      hello      
-------------------
-- Hello, Bunnies!
--(1 row)

Returning Sets
Set Returning Functions are supported via iterators:
CREATE OR REPLACE FUNCTION
my_favorite_things() RETURNS SETOF text LANGUAGE python AS
$$
import Postgres

only_a_few_of = [
    'raindrops on roses',
    'whiskers on kittens',
    'bright copper kettles',
    'warm woolen mittens',
    'brown paper packages tied up with strings',
]

def main(*args):
    # no args, actually..
    # And, we could just return the list, but
    # for the purpose of illustration, we'll use
    # a generator.
    #
    for x in only_a_few_of:
        yield x
$$;

SELECT * FROM my_favorite_things();
--            my_favorite_things             
---------------------------------------------
-- raindrops on roses
-- whiskers on kittens
-- bright copper kettles
-- warm woolen mittens
-- brown paper packages tied up with strings
--(5 rows)
Triggers
Trigger Returning Functions have multiple entry points:
CREATE OR REPLACE FUNCTION
tell_me() RETURNS TRIGGER LANGUAGE python AS
$$
from Postgres import NOTICE

def before_insert(td, new):
    NOTICE('inserted: ' + str(new))

def before_update(td, old, new):
    NOTICE('updated ' + str(old) + ' to ' + str(new))

def before_delete(td, old):
    NOTICE('deleted: ' + str(old))
$$;

CREATE TABLE paranoia (i int, t text);
CREATE TRIGGER watching_it BEFORE INSERT OR UPDATE OR DELETE
 ON paranoia FOR EACH ROW
 EXECUTE PROCEDURE tell_me();

INSERT INTO paranoia VALUES (0, 'ucme');
--NOTICE:  inserted: (0,ucme)
--INSERT 0 1

UPDATE paranoia SET i = 15;
--NOTICE:  updated (0,ucme) to (15,ucme)
--UPDATE 1

DELETE FROM paranoia;
--NOTICE:  deleted: (15,ucme)
--DELETE 1
Full Tracebacks
By managing code with modules, tracebacks are easy:
CREATE OR REPLACE FUNCTION
it_blows_up() RETURNS VOID LANGUAGE python AS
$$

def one():
    raise OverflowError("there's water everywhere")

def two():
    return one()

def three():
    return two()

# enters here:
def main():
    three()
$$;

SELECT it_blows_up();
-- ERROR:  function's "main" raised a Python exception
-- CONTEXT:  [exception from Python]
-- Traceback (most recent call last):
--    File "public.it_blows_up()", line 13, in main
--     three()
--    File "public.it_blows_up()", line 10, in three
--     return two()
--    File "public.it_blows_up()", line 7, in two
--     return one()
--    File "public.it_blows_up()", line 4, in one
--     raise OverflowError("there's water everywhere")
--  OverflowError: there's water everywhere
--
-- [public.it_blows_up()]
Composites
Composites are supported as a sequence and a mapping:
BEGIN;
CREATE TYPE foo AS (i int, t text);

CREATE OR REPLACE FUNCTION
fields_as_string(x foo) RETURNS TEXT LANGUAGE python AS
$$
template = """
i = {0!s}
t = {1!s}
0 = {2!s}
1 = {3!s}
"""

def main(x):
    return template.format(
        x['i'], x['t'], x[0], x[1],
    )
$$;

SELECT fields_as_string(ROW(-1,'kittens with mittens!')::foo);
--     fields_as_string      
-----------------------------
--                          +
-- i = -1                   +
-- t = kittens with mittens!+
-- 0 = -1                   +
-- 1 = kittens with mittens!+
-- 
--(1 row)

SELECT fields_as_string(ROW(57,NULL)::foo);
-- fields_as_string 
--------------------
--                 +
-- i = 57          +
-- t = None        +
-- 0 = 57          +
-- 1 = None        +
-- 
--(1 row)
ABORT;
Interrupts
Python FUNCTIONs can be interrupted:
postgres=# CREATE OR REPLACE FUNCTION infinite_loop() RETURNS text LANGUAGE python AS
postgres-# $$
postgres$# def main():
postgres$#  while 1:
postgres$#   pass
postgres$#  return 'never happens'
postgres$# $$;
CREATE FUNCTION
postgres=# SELECT infinite_loop();
^CCancel request sent
ERROR:  canceling statement due to user request
CONTEXT:  [exception from Python]
Traceback (most recent call last):
   File "__python__.infinite_loop()", line 4, in main
    pass
 Postgres.Exception

[__python__.infinite_loop()]
Credits
Copy of the latest authors file:

Primary Developers:
 James William Pye <x@jwp.io>

pg-python
execute Python code with PostgreSQL
pg-python is a Python 3 procedural language extension for PostgreSQL.
sample features
Functions are Modules Function code is managed using modules. Take advantage of an initialization section.
Native Typing Parameters given to functions are "Postgres data objects" with interfaces that suite the actual data type.
Full Tracebacks Never wonder where the exception came from again.
Set Returning Functions Supports both VPC and Materialization. Optimized Postgres.Cursor returns helps to eliminate Python interpreter overhead.
Direct Function Calls Avoid any SPI/parser/planner/executor overhead when calling simple functions.
Subtransactions Recover from database errors using the "xact()" context manager.
download
read docs
discuss