pg使用python编写存储过程,PostgreSQL PL / Python：在virtualenv中调用存储过程

When I call a PostgreSQL PL/Python stored procedure in my Python application, it seems to be executed in a separate process running as user postgres. So far, this only had the side effect that I had to make my logfile writable for both myself and the database user, so application and stored procedure can both write to it.

Now however, I started using virtualenv and added a number of .pth files to my ~/.virtualenvs/virt_env/lib/python2.7/site-packages/ folder that add the paths to my modules to the Python path.

When the stored procedure is executed, user postgres is not in the same virtual environment as I am, so the stored procedure does not find my modules. I can modify PYTHONPATH in the global PostgreSQL environment, but I have to change that every time I switch virtual environments - which is kinda against the purpose of virtualenv...

How can I extend the Python path for stored procedures ?

UPDATE:

A similar question has been asked and the resolution there was to modify the PYTHONPATH environment variable in Postgres; however, it seems that there is no standard way to specify environment variables for PostgreSQL; at least, it is not a viable solution on Mac OSX.

解决方案

There is a way to do it, as it turns out. Since version 1.6 or there abouts, virtualenv comes with a script activate_this.py, which can be used to set up an existing interpreter to access that particular virtualenv.

exec(open('/Some/VirtualEnv/Directory/myvirtualenv/bin/activate_this.py').read(),

dict(__file__='/Some/VirtualEnv/Directory/myvirtualenv/bin/activate_this.py'))

And as a fully-realized plpython function:

CREATE OR REPLACE FUNCTION workon(venv text)

RETURNS void AS

$BODY$

import os

import sys

if sys.platform in ('win32', 'win64', 'cygwin'):

activate_this = os.path.join(venv, 'Scripts', 'activate_this.py')

else:

if not os.environ.has_key('PATH'):

import subprocess

p=subprocess.Popen('echo -n $PATH', stdout=subprocess.PIPE, shell=True)

(mypath,err) = p.communicate()

os.environ['PATH'] = mypath

activate_this = os.path.join(venv, 'bin', 'activate_this.py')

exec(open(activate_this).read(), dict(__file__=activate_this))

$BODY$

LANGUAGE plpythonu VOLATILE

(The extra PATH mungery is needed since by default PATH isn't available in plpython os.environ -

activate_this.py has a fix checked in that should roll w/ the next point release (which should be 1.11.7 or 1.12)