この記事で行うこと

※（2019-11-20) pytestを使用して全面的に書き換えました。

PostgreSQLのユーザ定義関数（ストアドプロシージャ）をデバッグする気の利いた方法が分からなかったのですが、PostgreSQLはPythonでユーザ定義関数を書けるので、「ローカルのPython IDEでデバッグ後にCREATE FUNCTION文を発行すればいいよね」ということで、やってみました。
開発したPostgreSQLユーザ定義関数のソースコードをJupyter Notebookで表示します。

※ ユーザ定義関数のソースコード表示で参考にしたページ（感謝します）

ユーザ定義関数「foo」を作る

ローカルにて

まず、ローカルで開発し、後々PostgreSQLのユーザ定義関数にするfoo関数のソースコードを示します。プロジェクトの下にsrcサブディレクトリを作成して、その下にソースファイルを置きました。このfoo関数が今回のデバッグ＆テスト対象です。

src/pgfunc.py


def foo(num):
    def bar(n):
        return n * 2
    return bar(num) + 1

次に、pytestのフィクスチャとテストファイルを用意します。

まずはフィクスチャです。プロジェクト配下にtestsサブディレクトリを作成して、その下にソースファイルを置きました。

tests/conftest.py


import pytest
import psycopg2
import sys
import os
import configparser
from pathlib import Path

print("sys.path.append")
sys.path.append(os.path.join(os.path.dirname(__file__), '..'))

@pytest.fixture(scope="session")
def db_conn():
    # 設定ファイル読み込み
    conf = configparser.ConfigParser()
    conf.read(Path.cwd() / "tests/tests.ini")

    SECTION = "psycopg2"
    host = conf.get(SECTION, "host")
    port = conf.get(SECTION, "port")
    dbname = conf.get(SECTION, "dbname")
    user = conf.get(SECTION, "user")
    password = conf.get(SECTION, "password")

    # DB接続 ＆ テスト呼び出し
    with psycopg2.connect(f"host={host} port={port} dbname={dbname} user={user} password={password}") as conn:
        yield conn

フィクスチャで読み込んでいる設定ファイルは以下の通りです。

tests/tests.ini


[psycopg2]
host = postgres
port = 5432
dbname = default
user = default
password = secret

次にテストファイルです。testsの下にunitサブディレクトリを作成して、その下にソースファイルを置きました。test_foo関数でやっていることは、SQLの結果表のデータをfoo関数に与えて、戻り値を表示＆assert検証しています。

tests/unit/test_foo.py


from src import pgfunc

def test_foo(db_conn):
    sql = '''\
WITH hoge(num) AS (
    VALUES (1),
           (2),
           (3),
           (5),
           (7)
)
SELECT num
FROM hoge
ORDER BY num;
'''

    print()
    with db_conn.cursor() as cur:
        cur.execute(sql)
        for row in cur:
            num = row[0]
            result = pgfunc.foo(num)
            print(f"{result} = foo({num})")
            assert result == num * 2 + 1

ローカルのコマンドラインでpytestを使って実行すると、以下のように表示されてテストが通りました。

$ pipenv run pytest --capture=no
sys.path.append
============================= test session starts ==============================
platform darwin -- Python 3.7.4, pytest-5.3.0, py-1.8.0, pluggy-0.13.0
rootdir: /Users/username/PycharmProjects/project_foo
collected 1 item                                                               

tests/unit/test_foo.py 
3 = foo(1)
5 = foo(2)
7 = foo(3)
11 = foo(5)
15 = foo(7)
.

============================== 1 passed in 0.05s ===============================

PyCharmでデバッグする方法

事前にPyCharmのPreferences画面でpytestを使うように設定します。以下のように、Default test runnerをpytestにします。

foo関数内にブレークポイントを仕掛けた後、以下のようにtest_foo関数からデバッガを起動します。

DB側にて

ローカルでfoo関数を開発したら、以下のようにfooの頭と尻尾を書き換えます。

CREATE OR REPLACE FUNCTION foo(IN num INTEGER)
RETURNS INTEGER
AS $$
    def bar(n):
        return n * 2
    return bar(num) + 1
$$ LANGUAGE plpython3u;

上記CREATE文を発行し、ユーザ定義関数を作成後に使ってみます。
以下のSQLを実行します。


WITH hoge(num) AS (
    VALUES (1),
           (2),
           (3),
           (5),
           (7)
)
SELECT num,
       foo(num)
FROM hoge
ORDER BY num;

以下のように表示されました。意図通りにユーザ定義関数が動いています。

 num | foo 
-----+-----
   1 |   3
   2 |   5
   3 |   7
   5 |  11
   7 |  15
(5 rows)

Jupyter NotebookでPostgreSQLユーザ定義関数のソースコード表示

開発したfooのソースコードをJupyter Notebookで表示します。以下、有償版PyCharmでJupyter Notebookを開いて、そこからコードをコピペしました。


#%% md
# PostgreSQLのユーザ定義関数（ストアドプロシージャ）のソースコードを表示する

#%%
import psycopg2
from IPython.display import display

#%% md
## ユーザ定義関数のソースコードを取得する関数

第二引数：ユーザ定義関数名

参考ページ：http://everything-you-do-is-practice.blogspot.com/2017/09/postgresql-psql.html

#%%
def get_pgproc_src(conn, proc_name):
    with conn.cursor() as cur:
        cur.execute("SELECT pg_get_functiondef((SELECT oid FROM pg_proc WHERE proname = %s));", (proc_name,))
        src = cur.fetchone()
        return src[0].split('\n')

#%% md
## fooという名前のユーザ定義関数のソースコードを取得するSQL問い合わせ実行

#%%
with psycopg2.connect("host=postgres port=5432 dbname=default user=default password=secret") as conn:
    src_foo = get_pgproc_src(conn, 'foo')

#%% md
## fooのソースコード表示

#%%
display(src_foo)

このJupyter Notebookを実行した時のスクリーンショットは以下の通りです。

PostgreSQLユーザ定義関数：デバッグと、Jupyter Notebookでのソースコード表示