9月, 2019 | PostgresWeb - ポスグレウェブ

【PostgreSQL】カンマ区切り文字列の指定カンマ箇所を取得する

説明

カンマ区切り文字列の、指定したカンマ箇所を取得する

例： 'abc,def,ghi'のカンマ区切り2番目の文字を取得→"def"

引数

引数1(character varying)：対象の文字列

引数2(integer)：何番目のカンマ位置を取得するか

返り値

指定したカンマ位置の文字列

コード

CREATE OR REPLACE FUNCTION get_comma_char(
    character varying,
    integer)
  RETURNS character varying AS
$BODY$
declare

	c_target		    alias for $1; --引数1:対象の文字列
	i_target_index		alias for $2; --引数2:何番目のカンマ位置を取得するか

	i_loopcount             integer;
	i_commacount            integer;
	i_charposition          integer;
	i_charlength            integer;

begin

	i_commacount = 0;
	i_charposition = 1;
	i_charlength = 0;

	for i_loopcount in 1..length(c_target) loop
		if substring(c_target, i_loopcount, 1) = ',' then
			i_commacount = i_commacount + 1;

			if i_commacount = i_target_index then
				--指定箇所でループを抜ける
				exit;
			end if;

			i_charposition = i_charposition + i_charlength + 1;
			i_charlength = 0;

		else
			i_charlength = i_charlength + 1;
		end if;

	end loop;

	--コンマがないときnullで返す
	if i_commacount = 0 then
	return null;
	end if;

	return substring(c_target, i_charposition, i_charlength);
	
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION get_comma_char(character varying, integer)
  OWNER TO postgres;

※PostgreSQL12で動作確認済み

実行例

select * From get_comma_char('abc,def,ghi,jkl,mn',4);
--jkl

他にもこんな関数があります（文字列操作の関数）

・2つの文字列を比較して一致するかチェックする

・文字列をバイト数で計算する

・ひらがなをカタカナに置換する

・文字を左のX文字目から○文字切り取る（Mid関数）

・文字列中に指定した文字が含まれるかのチェック

【PostgreSQL】文字列の追加したい箇所に文字を入れる

文字列の追加したい箇所に文字を入れる

文字列の追加したい箇所に文字を入れる関数です。

引数

引数1(character varying)：対象の文字列（この文字列に文字を入れる）

引数2(integer)：何文字目に挿入するか

引数3(timestamp without time zone)：挿入する文字

返り値

挿入後の文字列

コード

CREATE OR REPLACE FUNCTION insert_char(
    character varying,
    integer,
    character varying)
  RETURNS character varying AS
$BODY$
declare

	c_target_char	alias for $1; 	--引数1:対象の文字列
	i_index		    alias for $2; 	--引数2:何文字目に挿入するか
	c_insert_char	alias for $3; 	--引数3:挿入する文字
	
BEGIN

	if i_index < 1 then
		return null;
	end if;

	return substring(c_target_char,1,i_index -1 ) || c_insert_char || substring(c_target_char,i_index ,length(c_target_char));
	
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION insert_char(character varying, integer, character varying)
  OWNER TO postgres;

※PostgreSQL9.5 , 9.6 , 10 , 11 , 12で動作確認済み　自作関数の実行方法はこちら

実行例

select * From insert_char('あいうえお',3,'かきくけこ'); 
--【結果】:あいかきくけこうえお

PostgreSQLの構文まとめを書いています

入門～経験者まで「基本構文・こんな時どう書くんだっけ？」のまとめ

【PostgreSQL】月末日を取得する（LAST_DAY）

月末日を取得する（LAST_DAY）

対象の日付から、月末の日を取得します。

例：2020/01/01を指定すると、2020/01/31が返ります。

使っている構文

月末を求めるためには、

例えば2月の月末日を求めたいなら、3月1日から1日引けば2月の月末日になるということです。

つまり引数の月の最初の日（○月1日）を取得し、+1月して、-1日すれば求めることができます。

そのためにこの関数では主に次の2つの構文を使っています。

① 月の最初の日を取得する

date_trunc('month',日付・タイムスタンプ)  -- 日付に対し、月の最初の日を取得する

② 月、日付を加算する

select current_date + cast('5 months' as INTERVAL);  --"2020-11-12 00:00:00"
select current_date + cast('5 days' as INTERVAL);    --"2020-06-17 00:00:00"

https://postgresweb.com/post-934

引数

引数1(timestamp with time zone)：月末を取得するタイムスタンプ

返り値

月末の日付（Date型）

コード

CREATE OR REPLACE FUNCTION last_day(timestamp with time zone)
  RETURNS date AS
$BODY$
declare
begin
    --引数の月の最初の日を取得、＋1月して、-1日することにより月末を取得する
	return date(DATE_TRUNC('month', $1) + '1 month' +'-1 Day');
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

※ PostgreSQL9.5 , 9.6 , 10 , 11 , 12で動作確認済み　自作関数の実行方法はこちら

実行例

select * from last_day(now());  --2019-09-30
select * from last_day(cast('2020/01/01' as date)); --2020-01-31

【PostgreSQL】日付の加算、週の加算、月の加算

日、週、月の加算をする

-- 日付の加算、週の加算、月の加算
日付 + cast( '5 days' as INTERVAL )       -- 5日加える
日付 + cast( '5 weeks' as INTERVAL )      -- 5週加える
日付 + cast( '5 months' as INTERVAL )     -- 5か月加える

-- 時間の加算、分の加算、秒の加算
日付 + cast( '5 hours' as INTERVAL )      -- 5時間加える
日付 + cast( '5 minutes' as INTERVAL )    -- 5分加える
日付 + cast( '5 seconds' as INTERVAL )    -- 5秒加える

-- マイナスを付けると減算になります
日付 + cast( '-5 days' as INTERVAL )      -- 5日前を求める

※ 日付はDate型、Timestamp型どちらでもOKです。

※ 例ではdaysのように複数形になっていますが、1 daysや2 dayと記述しても普通に動きます。

あわせて読みたい

日付の差を求める方法はこちらで紹介しています。

https://postgresweb.com/post-6433

日、週、月の加算例

日、週、月の加算例です。ここの例はそのままコピー＆貼り付けで実行できます。

日付の加算

-- 1.現在時刻に5日加える
select now() + cast('5 days' as INTERVAL);
-- 2020-07-16 08:00:00.000000+09


-- 2.『2020/1/1』に5日加える
select cast('2020/1/1' as date) + cast('5 days' as INTERVAL); 
-- 2019-01-06 00:00:00


-- 3.『2020/1/1 9:00:00』に5日加える
select cast('20200101 09:00:00' as timestamp) + cast('5 days' as INTERVAL); 
-- 2020-01-06 09:00:00

週の加算例

-- 1.現在時刻に5週加える
select now() + cast('5 weeks' as INTERVAL);
-- 2020-08-15 08:00:00.000000+09


-- 2.『2020/1/1』に5週加える
select cast('2020/1/1' as date) + cast('5 weeks' as INTERVAL); 
-- 2019-02-05 00:00:00


-- 3.『2020/1/1 9:00:00』に5週加える
select cast('20200101 09:00:00' as timestamp) + cast('5 weeks' as INTERVAL); 
-- 2020-02-05 09:00:00

月の加算例

-- 1.現在時刻に5ヶ月加える
select now() + cast('5 months' as INTERVAL);
-- 2020-12-11 08:00:00.000000+09


-- 2.『2020/1/1』に5ヶ月加える
select cast('2020/1/1' as date) + cast('5 months' as INTERVAL); 
-- 2019-06-01 00:00:00


-- 3.『2020/1/1 9:00:00』に5ヶ月加える
select cast('20200101 09:00:00' as timestamp) + cast('5 months' as INTERVAL); 
-- 2020-06-01 09:00:00

時、分、秒の加算例

時間の加算例

-- 1.現在時刻に5時間加える
select now() + cast('5 hours' as INTERVAL);
-- 2020-07-11 13:00:00.000000+09


-- 2.『2020/1/1』に5時間加える
select cast('2020/1/1' as date) + cast('5 hours' as INTERVAL); 
-- 2020-01-01 05:00:00


-- 3.『2020/1/1 9:00:00』に5時間加える
select cast('20200101 09:00:00' as timestamp) + cast('5 hours' as INTERVAL); 
-- 2020-01-01 14:00:00

分の加算例

-- 1.現在時刻に5分加える
select now() + cast('5 minutes' as INTERVAL);
-- 2020-07-11 13:00:00.000000+09


-- 2.『2020/1/1』に5分加える
select cast('2020/1/1' as date) + cast('5 minutes' as INTERVAL); 
-- 2020-01-01 00:05:00


-- 3.『2020/1/1 9:00:00』に5分加える
select cast('20200101 09:00:00' as timestamp) + cast('5 minutes' as INTERVAL); 
-- 2020-01-01 09:05:00

秒の加算例

-- 1.現在時刻に5秒加える
select now() + cast('5 seconds' as INTERVAL);
-- 2020-07-11 08:00:05.000000+09


-- 2.『2020/1/1』に5秒加える
select cast('2020/1/1' as date) + cast('5 seconds' as INTERVAL); 
-- 2020-01-01 00:00:05


-- 3.『2020/1/1 9:00:00』に5秒加える
select cast('20200101 09:00:00' as timestamp) + cast('5 seconds' as INTERVAL); 
-- 2020-01-01 09:00:05

【応用】月末日を求める

使う頻度はそう高くはないですが、日付を加える方法を使って月末日を求める方法があります。

例えば2月の月末日を求めたい時、3/1から1日前を求めればよいことになります。

-- 2021年2月（下の例では2021/2/14の日付から）の月末日を取得する
select date(DATE_TRUNC('month', cast('2021/2/14' as date)) + '1 month' +'-1 Day');
-- "2021-02-28"

あわせて読みたい

上の例で使用している関数はこちらで紹介しています。

https://postgresweb.com/postgresql-date-trunc

【PostgreSQL】日付を加算する（timestamp型）

日付を加算する関数

ある日付（＝引数１）に対し、指定した日数（＝引数２）を加算する関数です。

日付を加算する関数（引数：日付型）はこちら

引数

引数1(date)：加算する対象の日付(timestamp)

引数2(integer)：加算する日付

返り値

加算した後の日付

コード

CREATE OR REPLACE FUNCTION date_add(
    timestamp with time zone,
    integer)
  RETURNS timestamp with time zone AS
$BODY$
declare

	d_target  	alias for $1;	--引数1:加算する対象の日付
	i_add_day  	alias for $2;	--引数2:加える日数

begin

	return d_target + cast(cast(i_add_day as character varying) || ' days' as INTERVAL);
	
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION date_add(timestamp with time zone, integer)
  OWNER TO postgres;

※PostgreSQL12で動作確認済み

実行例

select * from date_add('2019/9/10 09:00:00',5);
--2019-09-15 09:00:00+09

select * from date_add('2019/9/10 09:00:00',-5);
--2019-09-05 09:00:00+09

他にもこんな関数があります（日付操作の関数）

・次の月曜日、火曜日、○曜日を求める(next_day関数)

【PostgreSQL】日付を加算する（Date型）

日付を加算する関数

ある日付（＝引数１）に対し、指定した日数（＝引数２）を加算する関数です。

日付を加算する関数（引数：timestamp型）はこちら

引数

引数1(date)　：加算する対象の日付

引数2(integer)：加算する日付

返り値

加算した後の日付

コード

CREATE OR REPLACE FUNCTION date_add(
    date,
    integer)
  RETURNS date AS
$BODY$
declare

	d_target  	alias for $1;	--引数1:加算する対象の日付
	i_add_day  	alias for $2;	--引数2:加える日数

begin

	return d_target + cast(cast(i_add_day as character varying) || ' days' as INTERVAL);
	
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION date_add(date, integer)
  OWNER TO postgres;

※ PostgreSQL12で動作確認済み

実行例

--'2019/9/5'に5日加算する
select * from date_add('2019/9/10',5);   --2019/9/15

--'2019/9/5'に-5日加算する（＝5日前を計算する）
select * from date_add('2019/9/10',-5);  --2019/9/5

他にもこんな関数があります（日付操作の関数）

・次の月曜日、火曜日、○曜日を求める(next_day関数)

【PostgreSQL】文字列のバイト数を取得する

文字列のバイト数を取得する

文字列をバイト数で計算します。

あわせて読みたい

自作関数ではなく構文はこちらを参照して下さい

https://postgresweb.com/post-4692

引数

引数1(character varying)：対象の文字列

引数2(integer)：全角のカウント方法の指定　

1：全角1文字 = 2バイトで計算　1以外：全角1文字 = 3バイトで計算

返り値

バイト数

コード

CREATE OR REPLACE FUNCTION count_char_byte(
    c_target_char character varying,
    i_flg integer)
  RETURNS numeric AS
$BODY$
declare
BEGIN
	if i_flg = 1 then
		--1:全角1文字=2バイトで計算
		return length(
		regexp_replace(
		    regexp_replace(c_target_char, '[ｱ-ﾝﾞﾟｧ-ｫｬ-ｮｰ｡｢｣､]', '_', 'g')
		    , '[^ -~]'
		    , '__', 'g')
		);
	else
		--その他:全角1文字=3バイトで計算
		return octet_length(c_target_char); 
        end if;
        
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION count_char_byte(character varying, integer)
  OWNER TO postgres;

※PostgreSQL9.5 , 9.6 , 10 , 11 , 12で動作確認済み　自作関数の実行方法はこちら

実行例

select * From count_char_byte('あいuえお',1); --全角1文字=2バイトで計算 
--【結果】:9
 
select * From count_char_byte('あいuえお',2); --全角1文字=3バイトで計算 
--【結果】:13

【PostgreSQL】pgAdmin4の日本語化

pgAdmin4を日本語化します。

「File」→「Preferences」をクリックします。

左のメニューから「Miscellaneous」→「User language」を選択。

右の画面より「User language」を「Japanese」に変更し、右下の「Save」をクリックします。

pgAdminを一度終了し、再度起動すると日本語化されていることが確認できます。

日本語化にならない時

上の方法をとっても日本語にならない現象が、（管理人も）発生しました。

結果的にインストール時に以下の1点を見直したところ日本語化できました。

※PostgreSQLのインストール時「Advanced Options」（画像の画面）、

Localeを「Japanese , Japan」を選択せず、初期値の[Default locale] でインストールします。

その上で、言語の設定を変更したところ日本語化されましたのでご参考まで。

https://postgresweb.com/introduction-to-postgresql

【PostgreSQL】2つの日付の差を計算する自作関数

概要

2つの日付（文字列）の差を計算します。

※ 標準の関数（age関数）を見たい場合はこちら

引数

引数1(character varying)：日付文字列1　※こちらが大きい(＝直近の)日付の想定

引数2(character varying)：日付文字列2

返り値

日付の差（数値）

コード

CREATE OR REPLACE FUNCTION datedif(
    character varying,
    character varying)
  RETURNS integer AS
$BODY$
declare

	a_date1		alias for $1;	--引数1:日付文字列1
	a_date2		alias for $2;	--引数2:日付文字列2

begin

	return cast(a_date1 as date) - cast(a_date2 as date);

end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION datedif(character varying, character varying)
  OWNER TO postgres;

実行例

--'20190910'と'20190901'の差を求める
select * From datedif('20190910','20190901');  -- 9

--'2019/9/10'と'2019/9/1'の差を求める
select * From datedif('2019/9/10','2019/9/1'); -- 9

--'20190930'と'20190901'の差を求める
select * From datedif('20190901','20190930');  -- -29

他にもこんな関数があります（日付操作の関数）

・次の月曜日、火曜日、○曜日を求める(next_day関数)

【PostgreSQL】ファイルパス（フルパス）からファイル名を取得する

ファイルパス（フルパス）からファイル名を取得する

ファイルパス（フルパス）からファイル名（拡張子付き）を取得します。

具体的には、後ろから'\'を検索し、その位置+1の文字から最後までを取得します。

「D:\folder1\folder2\text123.txt」（下線の部分を取得）

引数

引数1(character varying)：ファイルパスの文字列（フルパス）

返り値

ファイル名（拡張子付き）

コード

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

	full_path	alias for $1;		--引数1:ファイルパスの文字列（フルパス）

begin

	return substring(full_path,lastindexof(full_path,'\')+1,length(full_path));
	
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION get_filename(character varying)
  OWNER TO postgres;

※ PostgreSQL12で動作確認済み

※ この関数では「lastindexof」という関数が必要です。

あわせて読みたい

https://postgresweb.com/post-254

実行例

select * From get_filename('D:\folder1\folder2\text123.txt');
-- text123.txt

説明

引数

返り値

コード

実行例

他にもこんな関数があります（文字列操作の関数）

文字列の追加したい箇所に文字を入れる

引数

返り値

コード

実行例

PostgreSQLの構文まとめを書いています

月末日を取得する（LAST_DAY）

使っている構文

引数

返り値

コード

実行例

日、週、月の加算をする

日、週、月の加算例

日付の加算

週の加算例

月の加算例

時、分、秒の加算例

時間の加算例

分の加算例

秒の加算例

【応用】月末日を求める

日付を加算する関数

引数

返り値

コード

実行例

他にもこんな関数があります（日付操作の関数）

日付を加算する関数

引数

返り値

コード

実行例

他にもこんな関数があります（日付操作の関数）

文字列のバイト数を取得する

引数

返り値

コード

実行例

pgAdmin4を日本語化します。

日本語化にならない時

関連記事

概要

引数

返り値

コード

実行例

他にもこんな関数があります（日付操作の関数）

ファイルパス（フルパス）からファイル名を取得する

引数

返り値

コード

実行例