SQLサンプルコード (PostgreSQLで動作確認)

# 概要

タイトルの通りSQLのTips的なサンプルコードです。PostgreSQLで動作確認をしています。
内容的には個人的な備忘録になるかもしれませんが、随時更新していきます。

**環境**

* Windows10 Professional
* PostgreSQL 9.6.1

**参考**

* [PostgreSQL 9.6.2文書] (https://www.postgresql.jp/document/9.6/html/)
* [Springでトランザクション管理] (http://qiita.com/NagaokaKenichi/items/a279857cc2d22a35d0dd)


# スケジュール表

当日を起点として31日分の日付を返すカレンダー表を作成するサンプルコードです。
`GENERATE_SERIES`関数で0から30までの連番を生成しその値を当日日付に加算することでカレンダー表を生成しています。

```sql
CREATE OR REPLACE VIEW v_schedule (
    index,
    hiduke,
    youbi
) AS
WITH cal AS (SELECT DATE_TRUNC('day', NOW()) AS n),
     idx AS (SELECT GENERATE_SERIES(0, 30) AS i)
SELECT idx.i
     , cal.n + CAST(idx.i || 'days' AS INTERVAL)
     , EXTRACT(DOW FROM cal.n + CAST(idx.i || 'days' AS INTERVAL))
  FROM cal
     , idx
     ;
```

```sql
> SELECT * FROM v_schedule;
 index |         hiduke         | youbi
-------+------------------------+-------
     0 | 2017-03-13 00:00:00+09 |     1
     1 | 2017-03-14 00:00:00+09 |     2
     2 | 2017-03-15 00:00:00+09 |     3
     3 | 2017-03-16 00:00:00+09 |     4
     4 | 2017-03-17 00:00:00+09 |     5
     5 | 2017-03-18 00:00:00+09 |     6
     6 | 2017-03-19 00:00:00+09 |     0
     7 | 2017-03-20 00:00:00+09 |     1
     8 | 2017-03-21 00:00:00+09 |     2
     9 | 2017-03-22 00:00:00+09 |     3
    10 | 2017-03-23 00:00:00+09 |     4
    11 | 2017-03-24 00:00:00+09 |     5
    12 | 2017-03-25 00:00:00+09 |     6
    13 | 2017-03-26 00:00:00+09 |     0
    14 | 2017-03-27 00:00:00+09 |     1
    15 | 2017-03-28 00:00:00+09 |     2
    16 | 2017-03-29 00:00:00+09 |     3
    17 | 2017-03-30 00:00:00+09 |     4
    18 | 2017-03-31 00:00:00+09 |     5
    19 | 2017-04-01 00:00:00+09 |     6
    20 | 2017-04-02 00:00:00+09 |     0
    21 | 2017-04-03 00:00:00+09 |     1
    22 | 2017-04-04 00:00:00+09 |     2
    23 | 2017-04-05 00:00:00+09 |     3
    24 | 2017-04-06 00:00:00+09 |     4
    25 | 2017-04-07 00:00:00+09 |     5
    26 | 2017-04-08 00:00:00+09 |     6
    27 | 2017-04-09 00:00:00+09 |     0
    28 | 2017-04-10 00:00:00+09 |     1
    29 | 2017-04-11 00:00:00+09 |     2
    30 | 2017-04-12 00:00:00+09 |     3
(31 行)
```

GENERATE_SERIES関数は日付も受け取るので、下記のように日付のFROM、TOを渡すことでもカレンダー表を生成することができます。

```sql
SELECT hiduke
     , EXTRACT(DOW FROM hiduke) AS youbi
  FROM (SELECT GENERATE_SERIES(
                 DATE_TRUNC('day', now()),
                 DATE_TRUNC('day', now() + CAST('30 days' AS INTERVAL)),
                 '1 days') AS hiduke
       ) AS schedule
     ;
```


# カンマ区切りの値を扱う

あまり無いと思いますが1つのカラムにカンマ区切りで複数の値を格納しているようなテーブルがあって、そのカラムから任意の位置の値を取得したい場合のサンプルコードです。

下記のビューを使ってサンプルコードを作成しました。
sub_idsというカラムにカンマ区切りで複数のidが格納されているという想定です。

```sql
CREATE or REPLACE VIEW dummy (
    id,
    sub_ids,
    name
) AS
SELECT 1 AS id
     , '100,101' AS sub_ids
     , 'AAA' AS name
 UNION ALL
SELECT 2 AS id
     , '200,201,202' AS sub_ids
     , 'BBB' AS name
 UNION ALL
SELECT 3 AS id
     , '300' AS ids
     , 'CCC' AS name
;
```

```sql
> select * from dummy;
 id |   sub_ids   | name
----+-------------+------
  1 | 100,101     | AAA
  2 | 200,201,202 | BBB
  3 | 300         | CCC
(3 行)
```

Functionを作成すると簡単にカンマ区切りから任意の値を取り出すことが出来ます。

```sql
DROP FUNCTION ids_split(_ids VARCHAR, _pos INTEGER);

CREATE OR REPLACE FUNCTION ids_split(_ids VARCHAR, _pos INTEGER)
RETURNS INTEGER AS $$
  DECLARE
    _ret INTEGER;
    _len INTEGER;
  BEGIN

    SELECT CAST(d.ids_array[_pos] AS INTEGER)
         , ARRAY_LENGTH(d.ids_array, 1)
      INTO _ret
         , _len
      FROM (SELECT REGEXP_SPLIT_TO_ARRAY(_ids, ',') AS ids_array) AS d
         ;

    RETURN _ret;

  END;
$$ LANGUAGE 'plpgsql';
```

```sql
> select id, ids_split(sub_ids, 2), name from dummy where id = 2;
 id | ids_split | name
----+-----------+------
  2 |       201 | BBB
(1 行)
```


# パラメータの設定、確認

confファイルで設定するパラメータの設定、確認をpsqlコマンドで行う方法です。
設定を行うにはsetコマンド、確認するにはshowコマンドを使用します。

* [set] (https://www.postgresql.jp/document/pg960doc/html/sql-set.html)
* [show] (https://www.postgresql.jp/document/pg960doc/html/sql-show.html)


## 全設定を表示する

```sql
> show all
```

## client_encoding

```sql
> show client_encoding;
 client_encoding
-----------------
 SJIS
(1 行)
```

```sql
> set client_encoding = 'UTF8';
SET
```

## deadlock_timeout (integer)

> デッドロック状態があるかどうかを調べる前にロックを待つ時間をミリ秒で計算したものです。 

```sql
> show deadlock_timeout;
 deadlock_timeout
------------------
 10s
(1 行)
```

## log_lock_waits (boolean)

> セッションがロックの獲得までの間にdeadlock_timeoutより長く待機する場合にログメッセージを生成するかどうかを制御します。 

```sql
> show log_lock_waits;
 log_lock_waits
----------------
 on
(1 行)
```

## log_statement (enum)

> どのSQL文をログに記録するかを制御します。 有効な値は、none（off）、ddl、mod、およびall（全てのメッセージ）です。

```sql
> show log_statement;
 log_statement
---------------
 none
(1 行)
```


```sql
> show transaction isolation level;
 transaction_isolation
-----------------------
 read committed
(1 行)
```

## log_min_duration_statement



# トランザクションの分離レベルを確認、設定

* [トランザクションの分離] (https://www.postgresql.jp/document/9.6/html/transaction-iso.html)
* [SET TRANSACTION] (https://www.postgresql.jp/document/9.6/html/sql-set-transaction.html)

```sql
> show transaction isolation level;
 transaction_isolation
-----------------------
 read committed
(1 行) 
```

```sql
> begin;
BEGIN
> set transaction isolation level repeatable read;
SET
> commit;
COMMIT
```

トランザクションが始まっていない状態だと、下記のワーニングが表示されます。

> WARNING:  SET TRANSACTIONはトランザクションブロック内でのみ使用できます

# psql変数の確認、変更

setおよびechoメタコマンドを使用します。

## 設定されている変数を確認する

```sql
> \set
AUTOCOMMIT = 'on'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
VERBOSITY = 'default'
SHOW_CONTEXT = 'errors'
VERSION = 'PostgreSQL 9.6.1, compiled by Visual C++ build 1800, 64-bit'
DBNAME = 'test_db'
USER = 'rubytomato'
HOST = 'localhost'
PORT = '5432'
ENCODING = 'SJIS'
LASTOID = '0'
```

## autocommit

```sql
> \echo :AUTOCOMMIT;
on;
```

```sql
> \set AUTOCOMMIT off
```

# ロックの確認

* [明示的ロック] (https://www.postgresql.jp/document/9.6/html/explicit-locking.html)

ロックの一覧を確認するには[pg_locks] (https://www.postgresql.jp/document/9.6/html/view-pg-locks.html)システムテーブルを参照する。

```sql
SELECT a.pid
     , a.datname
     , a.usename
     , a.query
     , a.query_start
     , now() - a.query_start  AS waiting_duration
     , a.state
     , l.pid
     , l.transactionid
     , l.locktype
     , l.mode
     , l.granted
     , l.fastpath
  FROM pg_stat_activity a
     , pg_locks l
 WHERE a.pid = l.pid
   AND l.locktype = 'transactionid'
   AND (a.datname = 'test_db' OR a.datname IS NULL)
   AND l.pid <> pg_backend_pid()
    ;

  pid  | datname |  usename   |                   query                    |          query_start          | waiting_duration |        state        |  pid  | transactionid |   locktype    |     mode      | granted | fastpath
-------+---------+------------+--------------------------------------------+-------------------------------+------------------+---------------------+-------+---------------+---------------+---------------+---------+----------
 12164 | test_db | rubytomato | select * from todo where id = 1 for share; | 2017-04-19 00:04:03.124715+09 | 00:46:46.588213  | idle in transaction | 12164 |           585 | transactionid | ExclusiveLock | t       | f
(1 行)
```


# システム情報関数

* [システム情報関数] (https://www.postgresql.jp/document/9.6/html/functions-info.html)

## pg_backend_pid()

> 現在のセッションに結びついたサーバプロセスのプロセスID

```sql
> select pg_backend_pid();
 pg_backend_pid
----------------
          14404
(1 行)
```