説明

SELECT文で条件分岐をするにはCase文を使います。

ここではその構文と使用例を紹介します。

 

構文

CASE WHEN 条件1 THEN 値1
WHEN 条件2 THEN 値2
WHEN 条件3 THEN 値3
ELSE 値4 END AS 列名   --『AS 列名』は、あってもなくても可です。(ENDまでは必ず必要)
『WHEN 条件 THEN 値』をつなげていけばいくつでも条件を設定することができます。

 

あわせて読みたい

https://postgresweb.com/post-5291

 

 

 

使用例

次の例は「pg_table」のtablenameに対しcase文を使った例になります。

※そのまま実行できるので、コピーで実行し確認してみて下さい。

select 
    tablename,
    case when substring(tablename,5,1)= 'f' then 'a' -- (1)
    when substring(tablename,5,1)= 'p' then 'b'      -- (2)
    else 'c' end as flg                              -- (3)
from pg_tables
where schemaname != 'pg_catalog' order by tablename;

 

上の文の意味は、(1)の行で『tableの5文字目が'f'だったら'a'を表示する』、

(2)の行で『tableの5文字目が'p'だったら'b'を表示する』、

(3)の行で『(1)、(2)に合致しなければ'c'を表示する』としています。

またasを使っているので『これらの条件で表示する列名をflgとする』と書いています。

 

検索文字位置を取得する

文字列の中から、検索文字が何文字目にあるか検索するにはstrposを使用します。

見つからなかった場合は0が、見つかった場合は1以上の数値が返ります。

 strpos( 検索対象の文字列 , 検索する文字列)

 

 

使用例

select strpos('windows','d');                 -- 4(=4文字目にある)
select strpos('abcdefg','ef');                -- 5(=5文字目にある)
select strpos(upper('abcdefg'),upper('Cde')); -- 3(大文字小文字の区別なし)
select strpos('abcdefg','xyz');               -- 0(見つからない場合は0が返る)

 

 

ちょっとだけ緩く比較する

strposでは大文字小文字が区別されるので、区別しない+前後スペース除外した例を示します。

--検索対象と検索する文字列をそれぞれ、upperとtrimをかけて検索する
select strpos(trim(upper('ABCDEFGHIJKLMN')),trim(upper(' efg ')));  --5

 

 

関連記事(文字列操作)

文字列の結合、nullの結合

改行コードで結合、置換する

文字数のカウント(length)、バイト数を取得する

文字の切り取り(Left、Right、Substring)

lower(大文字を小文字に変換する)

全角半角スペースを除去する(trim)

upper(小文字を大文字に変換する)

大文字小文字を変換する、区別せず比較する

文字を置換する、複数置換する(replace、translate)

前0埋め、後ろ0埋め(lpad、rpad)

Nullの時に別の値に置き換える

nullif関数

小文字を大文字に変換する

小文字を大文字に変換するには、upperを使用します。

-- 小文字を大文字に変換する
upper( 変換したい文字列 )

 

 

実行例

upperの使用例です。※下の例はそのまま実行できます

-- 使用例 
select upper('abc');      --"ABC"
select upper('aBc');      --"ABC"
select upper('abc');   --"ABC"

 

 

注意点

全角小文字が大文字にならない時、ロケールの設定が「C」になっている可能性があります。(ロケールが「C」の時は、全角小文字がupperされません。)

その場合は、translateを使用して1文字ずつ変換すれば解決できます。

-- ロケールが「C」の時は、全角小文字がupperされない
select upper('abc');   --"abc"

-- translateを使用することで変換ができるようになります。
select translate('abc', 'abcdefghijklmnopqrstuvwxyz', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ');   --"ABC"

For文(ループ文)の書き方

PostgreSQLにおけるFor文の書き方です。

FOR 変数名 IN 初期値..終了値 LOOP
  --処理をここに書く
END LOOP;
あわせて読みたい

 

 

使用例

--1から10まで繰り返す
for i in 1..10 loop
	raise info '%' , i;
end loop;

※使用しているraise文はこちらで説明しています。

変数の値を画面に出す(raise)

 

 

ループ文の抜け方

ループ文から途中で抜けるには、EXITを使います。

--1から10まで繰り返す
for i in 1..10 loop

    --ループを抜けるIF文
	if i = 5 then
		exit; --exitでループを抜ける
	end if;

	raise info '%' , i;
end loop;

 

変数の値を画面に出す(raise)

FUNCTIONを作っているときに、変数の中身を見たい時があるとおもいますが、

その中身を表示するにはRaiseを使います。

-- 変数の中身を表示する(「%」に変数の値がセットされます)
raise info '%' , 変数名; 

-- 例
raise info '%' , num1;        -- 変数num1の中身を表示する
raise info 'num1=%' , num1;   -- このように書くと「num1=XX」と表示し、見やすくなります

 

変数の中身が表示された様子

変数の中身は「メッセージタブ」に表示されます。

「INFO:」の右に表示されているのが変数の値です。

※ selectしている「raise_test()」関数はこの次で紹介しています。

 

 

raise動作確認用の関数

raiseの動作を確認できるような関数を作成しました。(コピー&pgAdminに貼り付け→実行でOKです)

実行しても結果はnullが返りますが、メッセージタブに値が表示されているのが確認できます。

numの値を変えて実行すると、よりわかりやすくなると思いますのでお試しを。

CREATE OR REPLACE FUNCTION raise_test()
RETURNS character varying  LANGUAGE 'plpgsql'
COST 100 VOLATILE AS $BODY$
declare
	num numeric;
BEGIN

	num = 100; 
	raise info '%' , num; --変数numの中身を表示
	
 	num = 200; 
	raise info '%' , num; --変数numの中身を表示
	
 	num = 300; 
	raise info '%' , num; --変数numの中身を表示
	
	return null;

END;
$BODY$;

--実行するには次の文を実行して下さい
select * from raise_test();

 

あわせて読みたい

https://postgresweb.com/post-5260

 

 

エラーを取得する(EXCEPTION)構文

エラーが起こった時の処理を記述するには、『EXCEPTION』で記載します。

CREATE OR REPLACE FUNCTION err_test()
  RETURNS character varying AS
$BODY$
declare
        --省略
BEGIN
        --処理がここに書かれる(省略)

        EXCEPTION   --上の「処理」でエラーになった時、この下の「EXCEPTION」の処理が動きます
        WHEN OTHERS THEN
        --エラー時の処理をここに書きます

        --『SQLSTATE」にエラーコード 、 『SQLERRM』にエラーメッセージが格納されます
        return SQLSTATE || SQLERRM  ;
 
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

※PostgreSQL12で動作確認済み

 

 

実行例

関数「err_test()」を用意しましたので参考にして下さい。

エラーを発生させ、エラーコードとエラーメッセージが返るようになっています。

CREATE OR REPLACE FUNCTION err_test()
  RETURNS character varying AS
$BODY$
declare

	n_result numeric;
BEGIN

	n_result = 3/0; --ここで0で割り、わざとエラーを発生させる

	EXCEPTION
	WHEN OTHERS THEN
    -- エラーコードとエラーメッセージを返す
	return  'SQLSTATE 『' || SQLSTATE || '』 , ' || 'SQLERRM 『' || SQLERRM || '』';
 
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

 

上の関数を使った実行例を示します。

--実行例
select * from err_test(); 
--【結果】 "SQLSTATE 『22012』 , SQLERRM 『0 による除算が行われました』"

 

あわせて読みたい

この関数もEXCEPTIONを利用している関数です。使い方の例として参考になれば幸いです。

https://postgresweb.com/post-87

 

関数(FUNCTION)の作り方・書き方を解説する

ここでは関数(FUNCTION)を作る場合の書き方について、解説します。

ざっくりと各ブロックはこんな感じになります。(黄色のところを書くイメージ)

①~⑤の個所について説明していきます。

※ここで紹介している以外の書き方もありますが、一つの書き方として紹介します。

CREATE OR REPLACE FUNCTION 関数の名前(引数1 , 引数2 , 引数3 , ・・・)   …   ①

RETURNS 戻り値の型 AS      …   ②

$BODY$

declare

           引数を宣言する部分   …   ③

BEGIN

           処理を書く部分         …   ④

END;

$BODY$

LANGUAGE plpgsql VOLATILE

COST 100;

 

 

【①の箇所】関数の名前

①の黄色の箇所は関数名と引数の型を記述するところです。

CREATE OR REPLACE FUNCTION 関数の名前(引数1 , 引数2 , 引数3 , ・・・) 

 

例:

CREATE OR REPLACE FUNCTION testfnc( character varying , numeric , numeric)

 

 

【②の箇所】戻り値

RETURNS 戻り値の型 AS                 …   ②

 

例:

RETURNS character varying AS  …   文字型で返す

RETURNS integer AS          …   数値型で返す

 

 

【③の箇所】引数

引数は複数あった場合、「, 」(カンマ)でつなげて書きます。

CREATE OR REPLACE FUNCTION test_fnc(引数1 , 引数2 , 引数3)

 

例:

CREATE OR REPLACE FUNCTION test_fnc(character varying)           … 引数1つ

CREATE OR REPLACE FUNCTION test_fnc(numeric , character varying)     … 引数2つ

CREATE OR REPLACE FUNCTION test_fnc(character varying , numeric , integer) … 引数3つ

 

BEGINの前に引数を格納する変数名を書きます。

水色の「alias for $XX」でXX番目の引数かを書きます。

CREATE OR REPLACE FUNCTION 関数の名前(引数1 , 引数2 , 引数3 , ・・・)

・・・

declare

hensuu1     alias for $1;   …   引数1の値はこの変数に代入される   …   ③

hensuu2     alias for $2;   …   引数2の値はこの変数名に入される

hensuu3     alias for $3;   …   引数3の値はこの変数名に入される

BEGIN

・・・

 

 

【④の箇所】処理

④処理部分は目的に合わせた処理を記述しますが、最後は「return」で返り値を指定して下さい。

BEGIN

           処理を書く部分

           return 返り値;

END;

 

例:

BEGIN

           (中略)

           return 0;

END;

 

 

関数のテンプレート

上で紹介はしたものの、1から関数を書くのは手間です。

そのためコピー&貼り付けである程度済む、「関数テンプレート」を用意したので使ってみて下さい。

https://postgresweb.com/template-create-function

 

自作関数の実行の仕方

1~5の順番で実行してください。(詳しくは画像を参照)

1.自作関数のページからコードをコピーする

2.コピーしたコードをpgAdminの「クエリツール」へ貼り付け

3.貼り付けたコードを実行

4.自作関数のページから「実行例」のコードをコピーする

5.コピーしたコードをpgAdminの「クエリツール」へ貼り付け実行し確認する

 

以上が実行する手順です。

文字を日付に変換する

型を変換するにはcast(キャスト)を使用します。

日付への変換には「date」や「timestamp」を指定します。

-- 型を変換する
cast( A as B )  -- Aを型Bに変換する

-- 文字列を日付(date)に変換する
cast( 日付に変換する文字列 as date )

-- 文字列を日付時刻(timestamp)に変換する
cast( 日付に変換する文字列 as timestamp )

-- 書式を設定する
to_char(日付・日付時刻, 書式)

 

あわせて読みたい

書式設定で使用しているto_char()はこちらで紹介しています

https://postgresweb.com/post-4975

 

 

文字列を日付に変換する例

文字列 → 日付に変換する例

-- 1.文字列「20190401」を日付に変換する 
select cast('20190401' as date);              -- 2019-04-01 

-- 2.文字列「20190401」をtimestampに変換する 
select cast('20190401' as timestamp);         -- 2019-04-01 00:00:00 

-- 3.文字列「20190401 123456」をtimestampに変換する 
select cast('20190401 123456' as timestamp);  -- 2019-04-01 12:34:56 

 

文字列 → 日付に変換 + 書式設定をする例

-- 1.「20190401」をdateに変換し、「YYYY/MM/DD」形式へ変換する
select to_char(cast('20190401' as date), 'YYYY/MM/DD');  
-- 2019/04/01 

-- 2.「20190401 123456」をtimestampに変換し、「HH24:MI:SS」形式へ変換する
select to_char(cast('20190401 123456' as timestamp), 'HH24:MI:SS'); 
-- 12:34:56

-- 3.「20190401 123456」をtimestampに変換し、「YYYY/MM/DD HH24:MI:SS」形式へ変換する
select to_char(cast('20190401 123456' as timestamp), 'YYYY/MM/DD HH24:MI:SS'); 
-- 2019/04/01 12:34:56

 

数値文字列を「yyyy/mm/dd」形式にする例

-- 「20190401」を、「YYYY/MM/DD」形式へ変換する
select left('20190401',4) 
       || '/' || substring('20190401',5,2) 
       || '/' || right('20190401',2) ;
-- 2019/04/01 

 

あわせて読みたい

https://postgresweb.com/post-368

 

 

数値を日付に変換する

数値 → 日付に変換する例です。

数値→日付へ直接変換するとエラーとなるため、数値→文字→dateの順で変換しています。

-- 1.数値「20190401」を日付に変換する
select cast(cast(20190401 as character varying(8)) as date);  
-- 2019-04-01

-- 2.数値「20190401123456」を日付と時間に変換する(2行で1つの行です)
select cast(left(cast(20190401123456 as character varying(14)),8) || ' ' || 
       right(cast(20190401123456 as character varying(14)),6) as timestamp);
-- 2019-04-01 12:34:56

構文

--前0埋め( lpad )
lpad( 0埋めしたい文字列 , 何文字にするか ,'0')

--後ろ0埋め( rpad )
rpad( 0埋めしたい文字列 , 何文字にするか ,'0')

--数値の場合は文字に変換した後にlpad、rpadします
lpad( cast(0埋めしたい数値 as character varying) , 何文字にするか ,'0')

最後の引数『'0'』のところを別の文字に変えると、その文字で埋めるようになります。

「何文字にするか」は0からではなく、1からカウントします。

 

 

前0埋め

--前0埋め8桁
select lpad( '12345' , 8 , '0');    --結果:"00012345"

--前0埋め10桁
select lpad( '12345' , 10 , '0');   --結果:"0000012345"

--前スペース埋め10桁
select lpad( '12345' , 10 , ' ');   --結果:"     12345"

--数値の0埋め(数値の先頭に文字を付けるとエラーになるため、文字列にcastしています)
select lpad( cast(12345 as character varying), 8 , '0');  --結果:"00012345"

 

あわせて読みたい

https://postgresweb.com/post-342

 

 

 

後ろ0埋め

--後ろ0埋め8桁
select rpad( '12345' , 8 , '0');    --結果:"12345000"

--後ろ0埋め10桁
select rpad( '12345' , 10 , '0');   --結果:"1234500000"

--後ろスペース埋め10桁
select rpad( '12345' , 10 , ' ');   --結果:"12345     "

--数値の0埋め(castを使用)
select rpad( cast(12345 as character varying), 8 , '0');  --結果:"12345000"