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?

• XORcist: underdark.wordpress.com/2010/12/10/… tried this? Seems to work with version 8.4
• Jim: When I tried the first lanuage create I got this error: ERROR: function plpython_call_handler() does not exist
• Jim: BTW, that was using the new python 3.3 that I installed.
• Craig Ringer: I've written a better explanation for this here: stackoverflow.com/a/24218449/398670

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\postgres9円.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.

• user1062589: Yes, this worked for me as well. The proper version is critical.
• marcopolo: For Postgresql 9.6, which uses Python 3.3 it is essential to install python 3.3 from www.python.org. For Windows, make sure that the python33 path is added to the PATH environment variable. Read the next entry by jlujan for more details. I used Dependency Walker to figure out the details.

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

Answer 3

Postgres uses the ActiveState distros of Python. Most likely, your 2.5 is too outdated and Postgres cant load the plpython dll or didn't install it because there was no suitable python. I think recent postgres is buil6 against Python3 not 2.x. You can look in the postgres lib directory for plpython3.dll to find out what you need.

• Jim: I uninstalled 2.5 and installed 3.3 and I get the same errors. I do not see any plpython files in that dir. The dir is C:\Program Files (x86)\PostgreSQL9円.2\bin
• Jim: Sorry, that was the wrong dir. I did find in the lib dir this file: plpython3.dll
• Craig Ringer: Windows produces an error like plpython.dll: No such file or directory when it cannot load a dependency of plpython.dll. This error is confusing, but there isn't much we can do about it. I suggest opening plpython.dll with Dependency Walker from dependencywalker.com and verifying that the correct Python DLL is actually found. If it isn't, you might need to add the correct Python version to your system PATH.
• Jim: That wwas helpful! I received some errors when I tried it on the plpython3.dll file. It said it could not find the following files: LIBINTL.DLL, POSTGRES.EXE, PYTHON32.DLL. I am not sure how to fix this. Can I get a new plpython3.dll? Should I reinstall all of ppostgres? If I do will I lose the databases? In the past when I closed postgres I just hit the Windows close button... Do you think that caused the problem? Thanks, @Craig!
• Craig Ringer: libintl.dll and postgres.exe are in the PostgreSQL bin directory, so they'll be found just fine when PostgreSQL loads the module. The directory containing PYTHON32.DLL must be on the PATH. See stackoverflow.com/a/6318188/398670 . This means you must have Python 3.2, not 3.1, not 3.3, ...

Answer 4

I'm installed python 3.2(x64) y postgres 9.3 (x64)

Execute this code CREATE EXTENSION plpython3u

Do not forget to check the "installation-note.html" your version of postgresSQL there are specific python version for your postgresSQL

Answer 5

sorry for mining, but for all of those, who are looking for missing packet (in my case on Ubuntu 18.04):

sudo apt-get install -y postgresql-plpython3-10

and then:

create extension plpython3u;

• Ameer Ul Islam: But what will be CenOS equivalent command for this ? since CentOS doesn't have apt.
• Krzysztof Mochocki: @AmeerUlIslam i've never used centOS, but here is link to your RPM repo: centos.pkgs.org/7/postgresql-10-x86_64/…
• Ameer Ul Islam: it worked thanks. here is the code -> sudo yum install postgresql12-plpython3 (I'm using posgresql 12) . the repo it's pulling this from is pgdg12