PL/Python — Python Procedural Language

References

• PL/Python - Python Procedural Language

Notes

The PL/Python procedural language allows PostgreSQL functions and procedures to be written in the Python language.
To install PL/Python in a particular database, use CREATE EXTENSION plpython3u.

PL/Python is only available as an untrusted language, meaning it does not offer any way of restricting what users can do in it and is therefore named plpython3u. A trusted variant plpython might become available in the future iif a secure execution mechanism is developed in Python. The writer of a function is untrusted PL/Python must take care 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.

PL/Python Functions

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

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

The body of a function is simply a Python script. When the function is called, its arguments are passed as elements of the list args; named arguments are also passed as ordinary variables to the Python script. Use of named arguments is usually more readable. The result is returned from the Python code in the usual way, with return or yield (in case of a result-set statement). If you do not provide a return value, Python returns the default None., PL/Python translates Python's None into the SQL null value. In a procedure, the result form the Python code must be None; otherwise, an error will be raised.

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

The arguments are set as global variables. Because of the scoping rules of Python, this has the subtle consequence that an argument variable cannot be reassigned inside the function to the value of an expression that involves the variable name itself. It is best to treat function parameters as read-only.

Data Values

Generally speaking, the aim of PL/Python is to provide a natural mapping between the PostgreSQL and the Python worlds. This informs the data mapping rules described below.

When a PL/Python function is called, its arguments are converted from their PostgreSQL data type to the corresponding Python type.

When a PL/Python function returns, its return value is converted to the function's declared PostgreSQL return data type.

SQL array values are passed into PL/Python as a Python list. To return an SQL array value out of a PL/Python function, return a Python list.

Composite-type arguments are passed to the function as Python mappings. The element names of the mapping are the attribute names of the composite type.

Sharing Data

The global dictionary SD is available to store private data between repeated calls to the same function. The global dictionary GD is public data, that is available to all python functions within a session. Each function gets its own execution environment in the Python interpreter, so that global data and function arguments from myfunc are not available to myfunc2.