Pl Python

Overview

• PL/Python procedural language allows PostgreSQL functions and procedures to be written in the Python language.
• PL/Python is only available as an “untrusted” language, meaning it does not offer any way of restricting what users can do in it.
• Until a trusted variant plpython becomes available in the future, the writer of a function must take caution that the function cannot be used to do anything unwanted, since it will be able to do anything that could be done by a user logged in as the database administrator.

How to use

To install PL/Python in your database, Use CREATE EXTENSION plpython3u or CREATE EXTENSION plpython2u depending on the python version you want to use.

Installing 3rd party libraries in PL/Python

PL/Python by default comes with vanilla python installed. In order to use 3rd party modules:

• You must install the library in any of the following default PYTHONPATH locations:
  • /usr/lib/python38.zip
  • /usr/lib/python3.8
  • /usr/lib/python3.8/lib-dynload
  • /usr/local/lib/python3.8/dist-packages
  • /usr/lib/python3/dist-packages
• Or edit the PYTHONPATH variable of the postgresql server, i.e add the library installation path to etc/postgresql/<postgres_version>/main/environment.

For more details on PL/Python Environment Variables check Environment variables

Writing Functions in PL/Python

Functions in PL/Python are declared via the standard CREATE FUNCTION syntax

CREATE FUNCTION funcname (argument-list)
  RETURNS return-type
AS $$
  # PL/Python function body
$$ LANGUAGE plpython3u;

Example

A function that returns max of two numbers

CREATE FUNCTION pymax (a integer, b integer)
  RETURNS integer
AS $$
  if a > b:
    return a
  return b
$$ LANGUAGE plpython3u;

The Python code that is given as the body of the function definition is transformed into a Python function by the server like this

def __plpython_procedure_pymax_123456():
  if a > b:
    return a
  return b

Here 123456 is the OID assigned by POSTGRESQL to this function

Note: It is advised to treat the input variables to the function as READ-ONLY. If its necessary to reassign a value to the input, one must precede with the global keyword to obey the scope rules set in python.

Data Types Mapping

The following mapping is done internally by postgreSQL.

• PostgreSQL boolean is converted to Python bool.

• PostgreSQL smallint and int are converted to Python int. PostgreSQL bigint and oid are converted to long in Python 2 and to int in Python 3

• PostgreSQL real and double are converted to Python float.

• PostgreSQL numeric is converted to Python Decimal. This type is imported from the cdecimal package if that is available. Otherwise, decimal.Decimal from the standard library will be used. cdecimal is significantly faster than decimal. In Python 3.3 and up, however, cdecimal has been integrated into the standard library under the name decimal, so there is no longer any difference.

• PostgreSQL bytea is converted to Python str in Python 2 and to bytes in Python 3. In Python 2, the string should be treated as a byte sequence without any character encoding.

• All other data types, including the PostgreSQL character string types, are converted to a Python str. In Python 2, this string will be in the PostgreSQL server encoding; in Python 3, it will be a Unicode string like all strings.