PL/pgSQLとSQLのメモ

説明

これを見ればすべてが思い出せる。
そんな風に使いたい自分用メモです。

環境

モノ	バージョン
OS	Windows10 Home（64bit）
PostgreSQL	9.4.12
※localはCで、それ以外は全部でデフォルト

前提

ログ関係

とりあえずパラメータ確認

SHOW client_min_messages;
SHOW log_min_messages;

ログには出したくない。画面(client)で見れればいい。
そんな時は、NOTICEにしておけば、logがWARNINGなら出ないぞ。

対象	優先順位
client	DEBUG5、 DEBUG4、DEBUG3、DEBUG2、 DEBUG1、LOG、NOTICE、 WARNING、ERROR、FATAL、PANIC
log	DEBUG5、DEBUG4、 DEBUG3、DEBUG2、DEBUG1、 INFO、NOTICE、WARNING、 ERROR、LOG、FATAL、PANIC

※LOGの順序が違うので注意ね
※ログが文字化けしたら別問題
※ログはpowershellでtailしとく

powershell

Get-Content -Path "C:\Program Files\PostgreSQL\9.4\data\pg_log\aaa.log" -Tail 0 -Wait -Encoding UTF8

無名ハロワ

DO
$$
BEGIN
 RAISE NOTICE 'HELLO WORLD';
END;
$$

$$で囲まれたのが本体

ハロワ関数

CREATE OR REPLACE FUNCTION helloWorld()
RETURNS void AS $$
BEGIN
 RAISE NOTICE 'HELLO WORLD';
END;
$$ LANGUAGE plpgsql

※PROCEDURE オブジェクトは11から。

実行手順

• pgAdmin→SQLエディタ
• Terminal
  • ログインしつつ psql -U postgres -f hoge.sql
  • ログインの後に \i hoge.sql

その他

１０万回サイコロ

SELECT num
    ,  COUNT(*)
  FROM (
        SELECT format('Dicing eyes %s' ,(floor(random() * 6) + 1 )::int ) AS num
          FROM generate_series(1,100000) 
   ) as foo
 GROUP BY 1
 ORDER BY 1

num	count
Dicing eyes 1	16593
Dicing eyes 2	16657
Dicing eyes 3	16573
Dicing eyes 4	16567
Dicing eyes 5	16665

集約関数

WITH t(no,word) AS (
    SELECT '1','リンゴ' UNION ALL
    SELECT '2','ゴリラ' UNION ALL
    SELECT '3','ラッパ' 
) 
SELECT string_agg(word,'→' ORDER BY no) 
    ,  array_agg(word)
  FROM t 

結果
リンゴ→ゴリラ→ラッパ
{リンゴ,ゴリラ,ラッパ}

配列関数

WITH t(word) AS (
    SELECT ARRAY['リンゴ','ゴリラ','ラッパ']
) 
SELECT UNNEST(word)
  FROM t 

結果
リンゴ
ゴリラ
ラッパ