awesome-plpython

A collection of PL/Python scripts for PostgreSQL ranging from:

• educational examples
• fun experiments
• practical real-world utilities

---

⚠️ PL/Python Caveats

• PL/Python requires superuser/administrative privileges to install or create functions using plpython3u.
• Running Python code inside PostgreSQL can block sessions or impact performance if misused.
• Scripts using external Python libraries (cryptography, textblob) require the libraries to be installed in the database environment.
• Not recommended for high-throughput per-row computations; best suited for utility functions, small triggers, or demonstrations.
• Running servers or opening network sockets from PL/Python is for educational purposes only.

---

⚠️ Disclaimer

The provided Docker setup that boots a PostgreSQL instance with PL/Python enabled is intentionally simple and does NOT follow production best practices.

---

Quick Start

1. Clone the repository or download and unpack the zip archive

2. Start the PostgreSQL container

docker compose up -d

This will download an Alpine based PostgreSQL image from Docker Hub and enable PL/Python for it.

3. Ensure everything is running correctly

docker compose ps

4. Run a shell inside the container:

docker exec -it <container_name> sh

Or connect to psql client directly:

docker exec -it <container_name> psql -U pgawesome

Running PL/Python Scripts

Scripts are mounted inside the container at /var/opt/scripts. From psql shell it is possible to edit

\e /var/opt/scripts/fernet.sql

or execute the scripts

\i /var/opt/scripts/fernet.sql

The official documentation can further guide you through psql features.

Script Reference

Text Utilities

• diff.sql - Compare text with optional HTML diff output

Security / Cryptography

• fernet.sql - Symmetric encryption and decryption using Fernet
• sha3_512.sql - SHA3-512 hash
• token_urlsafe.sql - Secure random URL-safe token generation

Data Processing

• permutations.sql - An aggregate for computing all permutations of TEXT values
• sentiment_analysis.sql - Simple sentiment analysis function and sample usage

Demonstrations / Experiments

• ip_location.sql - External API integration for IP geolocation
• sqlite.sql - SQLite in PostgreSQL
• wsgi.sql - Minimal WSGI server running inside PostgreSQL (educational/demo only)

Resources

1. PL/Python documentation
2. Befriending an Elephant with a Snake: A Practical Guide to PL/Python
3. Getting Started with Postgres Functions in PL/Python

License

This project is licensed under the MIT License. See LICENSE for details.