[PostgreSQL] PL/Python

PL/Python을 이용하여 PostgreSQL의 데이터를 Python으로 다루거나 외부 데이터베이스의 데이터를 조회할 수 있다.

# apt-get install postgresql-plpython3-13

# psql -U admin postgres
psql (13.3 (Debian 13.3-1.pgdg100+1), server 13.15 (Debian 13.15-1.pgdg100+1))
Type "help" for help.

postgres=# CREATE EXTENSION plpython3u;

 

파이썬과 외부 모듈을 설치한다.

PostgreSQL, Oracle 그리고 Trino에 접속하기 위해 라이브러리 설치

# apt-get install python3 python3-pip python3-setuptools build-essential
# pip3 install --upgrade pip
# pip3 install numpy pandas requests psycopg2-binary oracledb trino

 

외부 PostgreSQL의 데이터를 조회하는 Function을 작성한다.

CREATE OR REPLACE FUNCTION postgres.get_other_postgres_data(param1 text, param2 text)
RETURNS TABLE(id integer, a text, b text, c text, d bool, e text)
LANGUAGE plpython3u
AS
$function$
import psycopg2
import pandas as pd

# 원격 PostgreSQL 연결 설정
conn= psycopg2.connect(
        dbname='postgres',
        user='admin',
        password='...',
        host='...',
        port='5432'
)

remote_df = pd.read_sql(f"SELECT id, a, b, c, d, e FROM tb_target WHERE a = '{param1}' AND b = '{param2}'", conn)

# 결과 반환 (행은 tuple로 구성함)
result = []
for row in remote_df.itertuples(index=False):
        result.append((row.id, row.a, row.b, row.c, row.d, row.e))

conn.close()

return result
$function$
;

 

외부 데이터베이스의 데이터를 조회할 수 있게 된다.

select *
  from postgres.get_other_postgres_data('A', 'B');