Skip to content

📚 plpython3u enabled

🔍 Prepare：

## OS and Software environment：

Environment name Version Download address
OS Rocky Linux 9.x Rocky Download
DB PostgreSQL 16.X PostgreSQL Download（YUM Repository）

## Download below packages（Example PG 16.8）：

| postgresql16-plpython3-16.8-1PGDG.rhel9.x86_64.rpm | Click Download RPM Package |

## purpose：DEV Member need get HTTPS data from API

alt text

alt text

🔍 introduction：

Plpython3u is an extension of PostgreSQL that allows developers to write stored procedures and functions in the Python language. By using plpython3u, Python code can be executed directly in the database, enabling more flexible and powerful functionality.

### 1. Download Package（postgresql16-plpython3）

alt text

### 2. Install Package（postgresql16-plpython3）

rpm -ivh /tmp/postgresql16-plpython3-16.8-1PGDG.rhel9.x86_64.rpm

alt text

### 3. Modify permissions

chown -R postgres. /usr/pgsql-16/

alt text

### 4. Create plpython3u Extension On postgres DB（If Other DB Need，Please Enter DB Execute）

su - postgres
psql -p 5432 -d postgres

CREATE EXTENSION "plpython3u";

alt text

### 5. Use Extension to get api data

CREATE OR REPLACE FUNCTION get_api_data(api_url text, xml_payload text)
RETURNS text AS $$
    import requests
    try:
        headers = {
            'Content-Type': 'application/json;charset=UTF-8',
            'Keep-Alive': 'timeout=1',
            'Content-Length': str(len(xml_payload))
        }
        response = requests.post(api_url, data=xml_payload, headers=headers, verify=False)
        if response.status_code == 200:
            return response.text
        else:
            return 'Error: ' + str(response.status_code)
    except Exception as e:
        return 'Error: ' + str(e)
$$ LANGUAGE plpython3u;

SELECT get_api_data('https://wimes-f130.wzs-sat-prd-p3.k8s.wistron.com/api/interface/test/Utilities/RequestToken', '{"userId": "cimdev","password": "123456","expiryDate": "2022-06-09T02:17:50.021Z"}');

alt text