概要
タイトルの通りSQLのTips的なサンプルコードです。PostgreSQLで動作確認をしています。
内容的には個人的な備忘録になるかもしれませんが、随時更新していきます。
環境
- Windows10 Professional
- PostgreSQL 9.6.1
参考
スケジュール表
当日を起点として31日分の日付を返すカレンダー表を作成するサンプルコードです。
GENERATE_SERIES関数で0から30までの連番を生成しその値を当日日付に加算することでカレンダー表を生成しています。
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
;
> 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を渡すことでもカレンダー表を生成することができます。
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が格納されているという想定です。
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
;
> select * from dummy;
id | sub_ids | name
----+-------------+------
1 | 100,101 | AAA
2 | 200,201,202 | BBB
3 | 300 | CCC
(3 行)
Functionを作成すると簡単にカンマ区切りから任意の値を取り出すことが出来ます。
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';
> 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コマンドを使用します。
全設定を表示する
> show all
client_encoding
> show client_encoding;
client_encoding
-----------------
SJIS
(1 行)
> set client_encoding = 'UTF8';
SET
deadlock_timeout (integer)
デッドロック状態があるかどうかを調べる前にロックを待つ時間をミリ秒で計算したものです。
> show deadlock_timeout;
deadlock_timeout
------------------
10s
(1 行)
log_lock_waits (boolean)
セッションがロックの獲得までの間にdeadlock_timeoutより長く待機する場合にログメッセージを生成するかどうかを制御します。
> show log_lock_waits;
log_lock_waits
----------------
on
(1 行)
log_statement (enum)
どのSQL文をログに記録するかを制御します。 有効な値は、none（off）、ddl、mod、およびall（全てのメッセージ）です。
> show log_statement;
log_statement
---------------
none
(1 行)
> show transaction isolation level;
transaction_isolation
-----------------------
read committed
(1 行)
log_min_duration_statement
トランザクションの分離レベルを確認、設定
> show transaction isolation level;
transaction_isolation
-----------------------
read committed
(1 行)
> begin;
BEGIN
> set transaction isolation level repeatable read;
SET
> commit;
COMMIT
トランザクションが始まっていない状態だと、下記のワーニングが表示されます。
WARNING: SET TRANSACTIONはトランザクションブロック内でのみ使用できます
psql変数の確認、変更
setおよびechoメタコマンドを使用します。
設定されている変数を確認する
> \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
> \echo :AUTOCOMMIT;
on;
> \set AUTOCOMMIT off
ロックの確認
ロックの一覧を確認するにはpg_locksシステムテーブルを参照する。
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 行)
システム情報関数
pg_backend_pid()
現在のセッションに結びついたサーバプロセスのプロセスID
> select pg_backend_pid();
pg_backend_pid
----------------
14404
(1 行)