plpython3u

PL/Python3U untrusted procedural language

plpython3u : PL/Python3U untrusted procedural language

Overview

ID	Extension	Package	Version	Category	License	Language
3290	plpython3u	plpython3u	`1.0`	LANG	PostgreSQL	C

Attribute	Has Binary	Has Library	Need Load	Has DDL	Relocatable	Trusted
--s-d--	No	Yes	No	Yes	no	no

Relationships
Schemas	`pg_catalog`
Need By	hstore_plpython3u jsonb_plpython3u ltree_plpython3u omni_python pg4ml
See Also	plperl plperlu pllua pltcl plsh pljava plr plxslt plpgsql plv8
Siblings	jsonb_plpython3u ltree_plpython3u hstore_plpython3u

Packages

PG18	PG17	PG16	PG15	PG14
1.0	1.0	1.0	1.0	1.0

This is a built-in contrib extension ship with the PostgreSQL kernel

Install

Create this extension with:

CREATE EXTENSION plpython3u;

Usage

plpython3u: PL/Python3 untrusted procedural language

PL/Python3U allows writing PostgreSQL functions in Python 3. It is an untrusted language with full access to the Python ecosystem. Only superusers can create functions.

CREATE EXTENSION plpython3u;

-- Simple function
CREATE FUNCTION py_hello(name text) RETURNS text
LANGUAGE plpython3u AS $$
  return f"Hello, {name}!"
$$;

SELECT py_hello('world');

-- Using Python standard library
CREATE FUNCTION py_sha256(data text) RETURNS text
LANGUAGE plpython3u AS $$
  import hashlib
  return hashlib.sha256(data.encode()).hexdigest()
$$;

-- Returning a composite type
CREATE TYPE address AS (street text, city text, zip text);

CREATE FUNCTION parse_address(raw text) RETURNS address
LANGUAGE plpython3u AS $$
  import re
  m = re.match(r'(.+),\s*(.+)\s+(\d{5})', raw)
  if m:
    return (m.group(1), m.group(2), m.group(3))
  return None
$$;

-- Set-returning function
CREATE FUNCTION py_generate_dates(start text, days int) RETURNS SETOF date
LANGUAGE plpython3u AS $$
  from datetime import datetime, timedelta
  d = datetime.strptime(start, '%Y-%m-%d')
  for i in range(days):
    yield (d + timedelta(days=i)).strftime('%Y-%m-%d')
$$;

-- Database access via plpy
CREATE FUNCTION py_row_count(table_name text) RETURNS bigint
LANGUAGE plpython3u AS $$
  result = plpy.execute(f"SELECT count(*) AS cnt FROM {table_name}")
  return result[0]['cnt']
$$;

-- Using external packages (must be installed on the server)
CREATE FUNCTION py_parse_json(url text) RETURNS jsonb
LANGUAGE plpython3u AS $$
  import json, urllib.request
  response = urllib.request.urlopen(url)
  data = json.loads(response.read())
  return json.dumps(data)
$$;