postgres and python

Question

In postgres 9.2 I am trying to create a python program that can be a trigger. I want to run an external program (an exe on the local disk) so I am using python to run it. When I try to create a simple program like this:

CREATE FUNCTION one () RETURNS int AS $$ # PL/Python function body $$ LANGUAGE plpythonu; 

I get the error:

ERROR:  language "plpythonu" does not exist HINT:  Use CREATE LANGUAGE to load the language into the database. 

When I run:

CREATE LANGUAGE plpythonu 

I get the error:

ERROR:  could not access file "$libdir/plpython2": No such file or directory 

I am using Windows 7 and python 2.5 .

I have looked in many places but cannot find a solution.

Any ideas?

Answer 1

I have just solved this problem, literally a few days back. The solution is quite involved. Here it goes.

1. Install python 3.2.* version only on your system.

2. In Postgresql use the 'CREATE LANGUAGE plpython3u' command to install Python 3 language support. More often than not, it will give the following error "unable to load ".....\plpython3.dll" error 126. (Note if it installs correctly, no error will be displayed.)

3. In case you get the above error, goto your python installation directory (default is C:\python32) and look for "python3.dll" in the DLL's folder. Copy this file to your Postgresql 'lib' folder in the installation directory of Postgres (default is c:\program files\postgres\9.x\lib\"). Rename this copied file to python32.dll.

4. Now run the 'CREATE LANGUAGE plpython3u' command again. It should work this time.

To verify, check out the pg_available_extensions view in the system tables of postgresql. The row containing plpython3u should have a version number in the 'installed version' column.

Note : This only works for plpython3u language. I do not know any similar process for plpython2u.

Answer 2

To resolve this for plpython3, it was necessary to:

• Install Python 3.2
• Run CREATE LANGUAGE plpython3u

Update: I've written a much better explanation here: https://stackoverflow.com/a/24218449/398670