PostgreSQL自作関数 | PostgresWeb - ポスグレウェブ

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

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

基準となる日付より後で、最初の指定した曜日の日付を取得します。

引数

引数1(date)：基準となる日付

引数2(integer)：取得したい曜日（0:日、1:月、2:火、3:水、4:木、5:金、6:土）

返り値

基準となる日付の翌日（当日は含まない）以後の日で、指定した曜日の日付

コード

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

	d_target	alias for $1;  --引数1:対象の日付
	i_weekday	alias for $2;  --引数2:取得する曜日（0:日 , 1:月 , 2:火 , 3:水 , 4:木 , 5:金 , 6:土）

	d_temp		date;
	
BEGIN

	for temp_count in 1..7
 	loop
    --日付を1日ずつ加算し、i_weekdayと一致したらリターンする
		d_temp = d_target + cast(cast(temp_count as character varying) || ' days' as INTERVAL);
		if date_part('dow', d_temp) = i_weekday then
		return d_temp;
		end if;
	end loop;
	

	return d_temp;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION next_day(date, integer)
  OWNER TO postgres;

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

実行例

select * from next_day('2019/9/23',2);
--2019/9/24（2019/9/23より後の最初の火曜日）

select * from next_day('2019/9/23',0);
--2019/9/29（2019/9/23より後の最初の日曜日）

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

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

【PostgreSQL】Decode関数

説明

OracleのDecode関数まがいのものです。

条件に合致した場合は指定した値が返ります。合致しない場合、最後の引数の値が返ります。

引数

引数1(character varying)：チェックする値

引数2(character varying[])：条件値（配列）

引数3(character varying[])：条件値に一致する場合の返り値（配列）

引数4(character varying)：条件に一致しない場合の返り値

返り値

引数3の値、（合致しない場合）引数4の値

コード

CREATE OR REPLACE FUNCTION public.decode(
	c_target character varying,
	a_conditions character varying[],
	a_return character varying[],
	c_else_return character varying)
    RETURNS character varying
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    
AS $BODY$
declare
/*
    引数1:チェックする値
    引数2:条件値
    引数3:条件値に一致する場合の返り値
    引数4:条件に一致しない場合の返り値
*/
BEGIN

	--配列の分だけループ
	for i in 1..array_length(a_conditions,1) loop
		if c_target = a_conditions[i] then
		    return a_return[i];
		end if;
	end loop;
	
	--条件に一致しない時、4つめの引数でリターン
	return c_else_return;

END;
$BODY$;

ALTER FUNCTION public.decode(character varying, character varying[], character varying[], character varying)
    OWNER TO postgres;

実行例

select decode('S'  , array['S','M','L'] , array['小','中','大'] ,'該当なし');   --小
select decode('M'  , array['S','M','L'] , array['小','中','大'] ,'該当なし');   --中
select decode('L'  , array['S','M','L'] , array['小','中','大'] ,'該当なし');   --大
select decode('XL' , array['S','M','L'] , array['小','中','大'] ,'該当なし');   --該当なし

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

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

【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"

引数

引数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】日付を加算する（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】文字列のバイト数を取得する

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

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

引数

引数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】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」という関数が必要です。

実行例

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