自作関数の実行の仕方

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

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

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

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

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

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

 

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

配列の指定箇所に値を入れる関数

配列の指定箇所に値を入れる(数値)

 

引数

引数1(character varying[]):挿入する配列

引数2(numeric):挿入する場所(1からカウント)

引数3(character varying):挿入する数値

 

返り値

値を挿入した後の配列

 

コード

CREATE OR REPLACE FUNCTION insert_into_array_num(
    numeric[],
    numeric,
    numeric)
  RETURNS numeric[] AS
$BODY$
declare
 
	a_array	        alias for $1; 	--引数1:挿入する配列
	insert_pos      alias for $2; 	--引数2:挿入する場所(1からカウント)
	c_insert_char	alias for $3; 	--引数3:挿入する数値

	return_array	numeric[];
	
begin

  
	for temp_count in 1..array_length(a_array,1)
	loop
		--返り値用の配列に値をセット
		if temp_count < insert_pos then
			--ループカウント < 引数2(insert_pos)の時、元の配列の値をセット return_array[temp_count] = a_array[temp_count]; end if; if temp_count = insert_pos then --ループカウント = 引数2(insert_pos)の時、引数3(挿入する文字列)をセット return_array[temp_count] = c_insert_char; end if; if temp_count > insert_pos then
			--ループカウント > 引数2(insert_pos)の時、元の配列の値(配列の添え字は-1)をセット
			return_array[temp_count] = a_array[temp_count -1] ;
		end if;

		if temp_count = array_length(a_array,1) then
			--最後に元の配列の最後の値をセット
			return_array[temp_count +1 ] = a_array[temp_count];
		end if;
	
	end loop;

	
	return return_array;
 
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION insert_into_array_num(numeric[], numeric, numeric)
  OWNER TO postgres;

 

実行例

--配列[123,234,345,456,567]の3番目に999を入れる
select * from insert_into_array_num(array[123,234,345,456,567],3,999)
--{123,234,999,345,456,567}

説明

配列の指定箇所に値を入れる(文字列)

 

引数

引数1(character varying[]):挿入する配列

引数2(numeric):挿入する場所(1からカウント)

引数3(character varying):挿入する文字列

 

返り値

値を挿入した後の配列

 

コード

CREATE OR REPLACE FUNCTION insert_into_array_char(
    character varying[],
    numeric,
    character varying)
  RETURNS character varying[] AS
$BODY$
declare
 
	a_array		    alias for $1; 	--引数1:挿入する配列
	insert_pos      alias for $2; 	--引数2:挿入する場所(1からカウント)
	c_insert_char	alias for $3; 	--引数3:挿入する文字列

	return_array	character varying[];
	
begin

  
	for temp_count in 1..array_length(a_array,1)
	loop
		--返り値用の配列に値をセット
		if temp_count < insert_pos then
			--ループカウント < 引数2(insert_pos)の時、元の配列の値をセット return_array[temp_count] = a_array[temp_count]; end if; if temp_count = insert_pos then --ループカウント = 引数2(insert_pos)の時、引数3(挿入する文字列)をセット return_array[temp_count] = c_insert_char; end if; if temp_count > insert_pos then
			--ループカウント > 引数2(insert_pos)の時、元の配列の値(配列の添え字は-1)をセット
			return_array[temp_count] = a_array[temp_count -1] ;
		end if;

		if temp_count = array_length(a_array,1) then
			--最後に元の配列の最後の値をセット
			return_array[temp_count +1 ] = a_array[temp_count];
		end if;
	
	end loop;

	
	return return_array;
 
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION insert_into_array_char(character varying[], numeric, character varying)
  OWNER TO postgres;

※PostgreSQL12で動作確認済み

 

実行例

select * from insert_into_array_char(array['A','B','C','D','E','F'],3,'Z')
--{A,B,Z,C,D,E,F}

説明

全角から半角へ変換する自作関数です。

自作関数を使わずSQLで変換する方法は一番下に記載しています。

 

あわせて読みたい

反対の半角→全角への変換はこちらです

https://postgresweb.com/post-2295

 

引数

引数1(character varying):変換する文字列

 

返り値

変換後の文字列

 

コード

CREATE OR REPLACE FUNCTION convert_full_to_half(c_full character varying)
  RETURNS character varying AS
$BODY$
declare
begin

	--アルファベットの変換
	c_full = translate(c_full,
	'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',
	'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
	);
	
	--数値の変換
	c_full = translate(c_full,
	'1234567890',
	'1234567890'
	);
		
	--カタカナ(濁音)
	c_full := replace(replace(replace(replace(replace(replace(c_full,'ヴ','ヴ' ),'ガ','ガ' ),'ギ','ギ' ),'グ','グ' ),'ゲ','ゲ' ),'ゴ','ゴ' );
	c_full := replace(replace(replace(replace(replace(c_full,'ザ','ザ' ),'ジ','ジ' ),'ズ','ズ' ),'ゼ','ゼ' ),'ゾ','ゾ' );
	c_full := replace(replace(replace(replace(replace(c_full,'ダ','ダ' ),'ヂ','ヂ' ),'ヅ','ヅ' ),'デ','デ' ),'ド','ド' );
	c_full := replace(replace(replace(replace(replace(c_full,'バ','バ' ),'ビ','ビ' ),'ブ','ブ' ),'ベ','ベ' ),'ボ','ボ' );
	c_full := replace(replace(replace(replace(replace(c_full,'パ','パ' ),'ピ','ピ' ),'プ','プ' ),'ペ','ペ' ),'ポ','ポ' );
	
	--カタカナ
	c_full = translate(c_full,
	'アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォヵヶッャュョヮ',
	'アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォカケッャュョワ'
	);
	
	--記号
	c_full = translate(c_full,
	' !”#$%&()ー-=^~|@‘「[{;+:*」]}、,<。.>・/?_',
	' !"#$%&()ー-=^~|@`「[{;+:*」]}、,<。.>・/?_'
	);
	
	c_full := replace(c_full,'’','''' );
	c_full := replace(c_full,'¥',chr(92) );
	
	return c_full;

end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

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

 

実行例

select * From convert_full_to_half('xyz890ュョワゴドボ/?_’¥'); 
--【結果】:"xyz890ュョワゴドボ/?_'\"

 

 

(関数を使わず)SQLで変換する方法

上の関数を使わずSQLで変換する例です。

少し長いのと、カナの濁音には対応していませんのでご注意ください。

-- 全角から半角へ変換するSQL文です
select translate('ここに変換したい文字を入れる',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890 !”#$%&()ー-=^~|@‘「[{;+:*」]}、,<。.>・/?_アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォヵヶッャュョヮ',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890 !"#$%&()ー-=^~|@`「[{;+:*」]}、,<。.>・/?_アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォカケッャュョワ'
);

-- 実行例
select translate('Az#アワ',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890 !”#$%&()ー-=^~|@‘「[{;+:*」]}、,<。.>・/?_アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォヵヶッャュョヮ',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890 !"#$%&()ー-=^~|@`「[{;+:*」]}、,<。.>・/?_アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォカケッャュョワ'
);
-- "Az#アワ"

説明

半角から全角へ変換する自作関数です。

自作関数を使わずSQLで変換する方法一番下に記載しています。

 

あわせて読みたい

反対の全角→半角への変換はこちらです

https://postgresweb.com/post-2297

 

 

 

引数

引数1(character varying):変換する文字列

 

 

返り値

変換後の文字列

 

 

コード

この関数はコピー&貼り付けで実行できます。

CREATE OR REPLACE FUNCTION convert_half_to_full(c_half character varying)
  RETURNS character varying AS
$BODY$
declare
begin

	--アルファベットの変換
	c_half = translate(c_half,
	'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',
	'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
	);
	
	--数値
	c_half = translate(c_half,
	'1234567890',
	'1234567890'
	);
		
	--カタカナ(濁音)
	c_half := replace(replace(replace(replace(replace(replace(c_half,'ヴ','ヴ' ),'ガ','ガ' ),'ギ','ギ' ),'グ','グ' ),'ゲ','ゲ' ),'ゴ','ゴ' );
	c_half := replace(replace(replace(replace(replace(c_half,'ザ','ザ' ),'ジ','ジ' ),'ズ','ズ' ),'ゼ','ゼ' ),'ゾ','ゾ' );
	c_half := replace(replace(replace(replace(replace(c_half,'ダ','ダ' ),'ヂ','ヂ' ),'ヅ','ヅ' ),'デ','デ' ),'ド','ド' );
	c_half := replace(replace(replace(replace(replace(c_half,'バ','バ' ),'ビ','ビ' ),'ブ','ブ' ),'ベ','ベ' ),'ボ','ボ' );
	c_half := replace(replace(replace(replace(replace(c_half,'パ','パ' ),'ピ','ピ' ),'プ','プ' ),'ペ','ペ' ),'ポ','ポ' );
	
	--カタカナ
	c_half = translate(c_half,
	'アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォカケッャュョワ',
	'アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォヵヶッャュョヮ'
	);
	
	--記号
	c_half = translate(c_half,
	' !"#$%&()ー-=^~|@`「[{;+:*」]}、,<。.>・/?_',
	' !”#$%&()ー-=^~|@‘「[{;+:*」]}、,<。.>・/?_'
	);
	c_half := replace(c_half,'''','’' );
	c_half := replace(c_half,chr(92),'¥' );
	
	return c_half;

end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

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

 

 

実行例

select * From convert_half_to_full('xyz890ュョアイウガギ/?_''\'); 
--【結果】:xyz890ュョアイウガギ/?_’¥

 

 

(関数を使わず)SQLで変換する方法

上の関数を使わずSQLで変換する例です。

少し長いのと、カナの濁音には対応していませんのでご注意ください。

-- 半角から全角へ変換するSQL文です
select translate('ここに変換したい文字を入れる',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890 !"#$%&()ー-=^~|@`「[{;+:*」]}、,<。.>・/?_アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォカケッャュョワ',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890 !”#$%&()ー-=^~|@‘「[{;+:*」]}、,<。.>・/?_アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォヵヶッャュョヮ'
);

-- 実行例
select translate('Az#アワ',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890 !"#$%&()ー-=^~|@`「[{;+:*」]}、,<。.>・/?_アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォカケッャュョワ',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890 !”#$%&()ー-=^~|@‘「[{;+:*」]}、,<。.>・/?_アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォヵヶッャュョヮ'
);
-- "Az#アワ"

説明

年月日の文字列から日付を作成する

 

引数

引数1(character varying):文字列(年)

引数2(character varying):文字列(月)

引数3(character varying):文字列(日)

 

返り値

文字から作った日付(数値に変換できなかった時はNullが返ります)

 

コード

CREATE OR REPLACE FUNCTION get_char_to_date(
    character varying,
    character varying,
    character varying)
  RETURNS date AS
$BODY$
declare

	c_year		alias for $1;	--引数1:文字列(年)
	c_month		alias for $2;	--引数2:文字列(月)
	c_day		alias for $3;	--引数3:文字列(日)

	c_temp		character varying;
	d_temp		character varying;
	c_execute	character varying;

begin

	--yyyy/mm/ddの文字列を作成
	c_temp = c_year || '/' || c_month || '/' || c_day;

	--日付変換できるかチェック
	c_execute := 'SELECT date(''' || c_temp || ''')';
 
	EXECUTE c_execute INTO d_temp;
	--日付だったらそのまま返す
	return d_temp;
	EXCEPTION
	WHEN OTHERS THEN
	--日付でなかったらnullで返す
	return null;

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

※PostgreSQL12で動作確認済み

 

実行例

select * from get_char_to_date('2019','4','1');
--"2019-04-01"

説明

引数の値以下で最大の整数値を返す関数です。

 

引数

引数1(numeric):丸める対象の数値

 

返り値

整数値

 

コード

CREATE OR REPLACE FUNCTION floor(numeric)
  RETURNS integer AS
$BODY$
declare
 
	a_target	alias for $1;	--引数1:丸める対象の数値
 
begin
 
	return trunc(a_target,0);
 
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION floor(numeric)
  OWNER TO postgres;

※PostgreSQL12で動作確認済み

 

実行例

select * from floor(200 * 0.977);
--195

バックアップ構文

バックアップするするコマンドは以下のようにします。

--バックアップするコマンド構文
pg_dump.exe -h ホスト名 -p ポート番号 -U ユーザー名 -f バックアップファイル保存先パス データベース名

--バックアップコマンド例 --データベース「db_school」のバックアップファイルを「C:\test\db_school.backup」というファイル名で作成する。
pg_dump.exe -h localhost -p 5432 -U postgres -f "C:\test\db_school.backup" db_school
 
ホスト名                              :localhostは固定で大丈夫です。
ポート番号                           :インストール時に変更していなければ、5432で大丈夫です。
ユーザー名                           :postgres固定で大丈夫です。
バックアップファイル保存パス:フルパスでバックアップのファイル名を自由に指定できます。
データベース名                     :バックアップを取りたいデータベース名を指定します。
 

実際にやってみる

最初にpg_dump.exeがPC上のどこにあるか確認します。

特に指定なくインストールした場合は、画面の箇所にあると思います。

①のpg_admin.exeを確認したら、そのフォルダ(②)をコピーしておきます。

 

次にコマンドプロンプトを起動します。

キーボード左したのWindowsボタンを押しながら「R」を押して下さい。

下の画面が起動するので①に「cmd」と入力し②のOKボタンを押します。

 
 
コマンドプロンプトが開いたら
「cd」+半角スペースを入力後、pg_dump.exeのフォルダを入力します。
※画面の場合は「cd C:\Program Files\PostgreSQL\9.4\bin」
 
 
 
フォルダを入力したらENTERキーを押すと、下の画面のようになります。
 
 
赤字の箇所に上にあるバックアップ構文を入力しENTERを押します。
 
 
 
画面のようになったらOKです。
 
 
フォルダを確認するとファイルができていることを確認してください。
 
 

bat(バッチファイル)でやってみる

上のコマンドをbatファイルにしてダブルクリックでバックアップが実行できるようにします。

 

メモ帳を開き構文を入力します。

構文は上で使用したcdのコマンド(1行目)と

バックアップのコマンド(2行目)です。

 

ファイル名は「test.bat」としておきましょう。

testの箇所は何でも構いませんが、拡張子は「.bat」としてください。

※画面はデスクトップに作った画像です。

 
 

この作ったバッチをダブルクリックし、

バックアップファイルができていることが確認できればOKです。

文字を日付に変換する

型を変換するには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

配列が全て空かチェックする

配列の中身が全て空なのかチェックする

(Nullまたは""の時、空であると判定します。)

 

引数

引数1(character varying[]):チェックする配列

 

返り値

True:全て空である、False:空でない

 

コード

CREATE OR REPLACE FUNCTION check_empty_array(character varying[])
  RETURNS boolean AS
$BODY$
declare

	a_array		alias for $1; 	-- 引数1:チェックする配列
	b_flg		boolean;	    -- True:全て空である、False:空でない

begin

	b_flg = true;

	for loop_count in 1..array_length(a_array,1)
 	loop
		--Nullまたは""の時、空であると判定
		if (a_array[loop_count] = '') or (a_array[loop_count] is null) then
		else
			b_flg = false;
		end if;
	end loop;

	return b_flg;
 
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION check_empty_array(character varying[])
  OWNER TO postgres;

※ PostgreSQL12で動作確認済み

 

実行例

select * From check_empty_array(array['',null,null,'']);
-- true(空である)

select * From check_empty_array(array['a','b','c']);
-- false(空でない)

 

他にもこんな関数があります(配列操作の関数)

配列に特定の文字列が含まれるかをチェックする

2つの配列を結合する