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.

More information on PL/Python Basics

• ####Data Mapping in PL/Python