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

# この記事で行うこと

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

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

### ※ ユーザ定義関数のソースコード表示で参考にしたページ（感謝します）
[PostgreSQL の psql で関数のソースコードを取得する](http://everything-you-do-is-practice.blogspot.com/2017/09/postgresql-psql.html)

# ユーザ定義関数「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にします。

<img width="1016" alt="スクリーンショット 2019-11-20 16.27.05.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/4784f74c-66ba-d082-4472-38831eabe66d.png">

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

<img width="589" alt="スクリーンショット 2019-11-20 16.28.02.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/dcb32e38-5cc2-e409-fba8-f33d9de458ad.png">


## 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を実行します。

```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を開いて、そこからコードをコピペしました。

```py

#%% 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を実行した時のスクリーンショットは以下の通りです。

![スクリーンショット 2019-05-09 15.23.48.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/327312/9aa64095-bac8-db99-e5ad-344617e02294.png)