Can pl/python be used to write postgres functions in an azure hosted db?

Question

Is it possible to write postgres functions in pl/python on an azure hosted db?

Postgres allows python, but I read that creating python functions requires superuser; however, I also read that azure postgres does not expose superuser account.

Answer 1

PostgreSQL supports python in database scripts using plpython3u extension. Unfortunately, this extension is not listed until today 05/2023 neither into single or flexible server on Azure.

What you can do as an alternative is the following:

1. option1, to use a virtual machine with linux operating system, install postgresql and install manually the plpython3u extension.
2. option2, to run postgresql into docker container hosted in Azure Kubernetes, install postgresql and install manually the plpython3u extension.

The above solutions are possible, but you have to maintain the resource allocation, disk and log maintenance properly in contrast to managed instances.

Here are the steps to install plpython3u:

--INSIDE POSTGRESQL--
SELECT * FROM pg_available_extensions

--------------------------------------------------------------------------------------

--INSIDE VM--
apt-get update
apt-get install postgresql-plpython3-14 #FOR PG_14
apt-get install postgresql-plpython3-15 #FOR PG_15

--------------------------------------------------------------------------------------

--RESTART THE POSTGRESQL INSTANCE--

--------------------------------------------------------------------------------------

--INSIDE POSTGRESQL--
SELECT * FROM pg_available_extensions

SELECT * FROM pg_language
CREATE EXTENSION plpython3u

--------------------------------------------------------------------------------------

--INSIDE POSTGRESQL--
CREATE OR REPLACE FUNCTION number_max (a integer, b integer)
RETURNS integer
AS $$
  if a > b:
    return a
  else:
    return b
$$
LANGUAGE plpython3u;

SELECT number_max (3,5)

I hope some day to see the plpython3u extension into available list of Azure Flexible Server Extensions!