PostgreSQLを入れてからパスワード認証で使えるようにしてC言語で関数を作って取り込むまで

C言語で実装した関数が使えないことが分かったので，ここに書き捨てることにする．
ついでに動作確認に手元へpsqlの環境を作ったので合わせてメモをしておく．

# PostgreSQL

## インストール

```sh:
sudo apt-get install postgresql-9.5 postgresql-server-dev-9.5
```

## 設定

- パスワードを設定するsqlを走らせる．
  パスワードがコマンドラインヒストリに残るのが気にするなら，ファイルを作って実行した方がいいかも．下の例はのパスワードは`postgres'

    ```sql:create_password.sql
    ALTER ROLE postgres WITH PASSWORD 'postgres';
    ```

    ```sh:
    sudo su postgres -c 'psql < create_password.sql'
    ```

- パスワード認証するよう設定

    ```diff:
    --- pg_hba.conf.orig
    +++ pg_hba.conf
    @@ -82,7 +82,7 @@
     # maintenance (custom daily cronjobs, replication, and similar tasks).
     #
     # Database administrative login by Unix domain socket
    -local   all             postgres                                peer
    +local   all             postgres                                md5
     
     # TYPE  DATABASE        USER            ADDRESS                 METHOD
    ```

- postgresqlの再起動

    ```sh:
    sudo systemctl restart postgresql.service
    ```

- ログインできる

    ```sh:
    psql -U postgres
    ```

# 関数

## DB作る，TBL作る

-dオプションは-dをつけなくてもいいけど．

```sql:create_tbl.sql
DROP TABLE IF EXISTS _test;

CREATE TABLE _test (
    _value  REAL
);
```

```sh:
sudo su postgres -c 'createdb testdb'
psql -U postgres -d testdb < create_test.sql
```

## データを入れる

```python:make_data.py
import numpy as np
from psycopg2 import connect

dbattr = {'user':'postgres',
        'password':'postgres',
        'host':'localhost',
        'dbname':'testdb'}

data = [[val] for val in map(float, np.random.normal(10,10,size=100))]
with connect(**dbattr) as con:
    cur = con.cursor()
    cur.executemany('INSERT INTO _test (_value) VALUES (%s)',data)
    con.commit()
```

## 関数実装してビルド

_idごとに_valueを合計して，和が大きい順に順位を返す関数を実装することにする．

```c:mypos.c
#include "postgres.h"
#include "fmgr.h"

/* 値が
           <0   <-- -1 を返す
   0<= and <5   <--  0 を返す
   5<= and <10  <--  5 を返す
   10<=         <-- 10 を返す
   のいずれに該当するか判定して返す */

PG_FUNCTION_INFO_V1(mypos);

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

extern Datum mypos(PG_FUNCTION_ARGS);

/* 判定位置 */
int thresholds[3]   = {0, 5, 10};

/* 返り値リスト */
int returnvalues[4] = { -1, 0, 5, 10 };

Datum mypos(PG_FUNCTION_ARGS)
{
    float4 x = PG_GETARG_FLOAT4(0);

    int i;

    for (i=0; i<3; ++i)
        if (x<thresholds[i])
            break;

    PG_RETURN_INT32(returnvalues[i]);
}

```

```basemake:Makefile
CC := gcc
CFLAGS := -shared -I /usr/include/postgresql/9.5/server -lpq -fPIC -O3

all: mypos.o

mypos.o: mypos.c
	$(CC) $(CFLAGS) -o $@ $^

clean:
	rm -f *.o
```

## 関数登録

```sql:mypos.sql
CREATE OR REPLACE FUNCTION mypos(REAL)
RETURNS INTEGER
AS '/tmp/mypos.o'
LANGUAGE C
WITH (isStrict);
```

```sh:
make
psql -U postgres -d testdb < mypos.sql
```

## 使ってみる

```sh
psql -U postgres -d testdb -c 'SELECT _value, mypos(_value) from _test'
```

## 結果

```
  _value   | mypos 
-----------+-------
   24.7577 |    10
  -8.12542 |    -1
   3.29176 |     0
   10.6288 |    10
   5.76772 |     5
   8.46606 |     5
   8.84471 |     5
  -0.35494 |    -1
   25.6629 |    10
   11.2771 |    10
   6.81214 |     5
   4.78453 |     0
   5.88952 |     5
   25.6756 |    10
   3.30392 |     0
   1.73068 |     0
   1.61298 |     0
   27.7148 |    10
   4.05851 |     0
  0.154022 |     0
  -7.96923 |    -1
   21.3401 |    10
    8.5594 |     5
   16.3689 |    10
    14.203 |    10
     4.584 |     0
(stdin):
```