Learning the Basics of PL/Python Postgres Functions

One of the procedural languages included in the default PostgreSQL releases is Python. In this lesson, we'll briefly explore how to start creating Postgres functions using PL/Python.

User-defined functions in Postgres consist of code segments that are stored for future use, similar to programming functions in general. Each time a function is called, it performs a specific task. The ability to create these functions in languages beyond standard SQL is a significant advantage for app or web developers. A commonly used language is PL/pgSQL, which is an extension of SQL that adds new syntax for data processing rather than just querying.

However, there are instances where it might be more beneficial to use a different language, such as Python, R, or Java. Python and R are popular choices for data analysis. While PL/pgSQL can manage some of the processing tasks you need, leveraging the specialized tools available in Python or R can simplify your work significantly.

Python is particularly strong in scientific computing, thanks to its extensive libraries, communities, and ecosystems dedicated to this field, such as SciPy, NumPy, and Pandas. It also boasts a wealth of open-source packages widely used across various industries.

In essence, using PL/Python allows you to enhance Postgres in ways that PL/pgSQL may not support as effectively. Moreover, if you're already comfortable with Python, PL/Python is a practical option that saves you from having to learn a new language.

I want to emphasize that PL/Python is specifically designed to work with Python embedded within Postgres. Many of you might be using an object-relational mapper (ORM), like Django, to connect Python and Postgres from your Python environment, either through a standard driver such as psycopg2 or in a more advanced manner. If you're new to Postgres, as I am, it's important to know that PL/Python serves various purposes in different scenarios.

I’ll assume you already have a PostgreSQL database set up. While PL/Python is a core part of PostgreSQL, you first need to ensure that Python is installed on the same system (it may or may not come pre-installed with your operating system). For your convenience, the Postgres container available on the Crunchy Developer Portal includes Python. Your routines will need to call the server's Python interpreter. Additionally, you must have superuser access to load PL/Python into your database. To do this, you need to:

CREATE EXTENSION plpython3u;

Python 3 will be utilized by plpython3u, while Python 2 will be the default if you install using the generic name plpythonu. Even if you plan to use only one version, it's beneficial to read the section comparing Python 2 and Python 3. The official documentation mentions that both versions can be used in the same database, but they must operate in separate sessions.

Since Python 2.7 (the final release in the 2.x series) is deprecated, it's advisable to stick with plpython3u for now.

PL/Python is considered an "untrusted" language in Postgres because it allows Python code to access the host machine's filesystem. This presents a greater risk compared to using a trusted language like PL/pgSQL, as indicated by the "u" in its name.

To create PL/Python functions, you need superuser privileges. One approach is to use PL/Python as a trusted language in a separate development environment. This allows other users to create functions while the application is still being developed, enabling proper testing and vetting before moving them into production. This workflow might work well for you, but proceed with caution!

Let's create a function that returns a table with the count, mean, standard deviation, and minimum of an array of numbers using pandas and numpy.

create or replace function agg_tbl(x numeric[])
returns table(count float, mean float, std float, min float)
as $$
    import pandas as pd
    import numpy as np
    data=pd.Series(x)
    count=data.describe()[0]
    mean=data.describe()[1]
    std=data.describe()[2]
    min=data.describe()[3]
    return np.array([count, mean, std, min]).reshape(1,-1)
$$ language plpython3u;

Lets check the output

SELECT * FROM agg_tbl(ARRAY[1,2,3]);
 count | mean | std | min
-------+------+-----+-----
     3 |    3 |   1 |   1
(1 row)

To use urllib.parse, you only need to include the import statement in your function code since it's a built-in module. This demonstrates that using PL/Python can be simpler and faster than trying to achieve the same results with standard SQL or PL/pgSQL.

What about third-party packages like SciPy, NumPy, or Pandas? Generally, you can import them as you would normally, provided the package is installed on the same system as Python. Instead of sending data to an external client (which might require converting it to another format), consider having PL/Python manage some of the more complex tasks right where the data resides.