Playing with PL/Python

Prerequisites:

sudo apt-get install postgresql-plpython-9.3
# must be superuser to create procedural language "plpythonu"
sudo -Hnu postgres psql -c "ALTER USER felix WITH SUPERUSER"
sudo -Hnu postgres psql -c "CREATE EXTENSION plpythonu"
# or for a certain DB:
sudo -Hnu postgres psql $dbname -c "CREATE EXTENSION plpythonu"

Simple function that returns a table:

CREATE FUNCTION store_foo()
    RETURNS TABLE (id int, name text)
AS $$
    return (
        dict(id=i, name='foo-{0}'.format(i))
        for i in range(1000)
    )
$$ LANGUAGE plpythonu;

select * from store_foo();

Importing modules from anywhere at runtime

The cleaner solution would be to set PYTHONPATH for the postgresql server process.

Example module:

mkdir -p /tmp/foo
cat <<EOF > /tmp/foo/foo.py
def f():
    return 'hello from foo'
EOF

Function:

CREATE FUNCTION foo_module()
    RETURNS text
AS $$
    import sys
    sys.path.insert(0, '/tmp/foo')
    import foo
    return foo.f()
$$ LANGUAGE plpythonu;

select foo_module();

Multicorn

http://multicorn.org/

Prerequisites:

sudo apt-get install postgresql-server-dev-9.3
sudo pip install pgxnclient
sudo pgxn install multicorn