自作関数の実行の仕方
1~5の順番で実行してください。(詳しくは画像を参照)
1.自作関数のページからコードをコピーする
2.コピーしたコードをpgAdminの「クエリツール」へ貼り付け
3.貼り付けたコードを実行
4.自作関数のページから「実行例」のコードをコピーする
5.コピーしたコードをpgAdminの「クエリツール」へ貼り付け実行し確認する
以上が実行する手順です。
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で変換する方法は一番下に記載しています。
引数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文です
select translate('ここに変換したい文字を入れる',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890 !”#$%&()ー-=^~|@‘「[{;+:*」]}、,<。.>・/?_アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォヵヶッャュョヮ',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890 !"#$%&()ー-=^~|@`「[{;+:*」]}、,<。.>・/?_アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォカケッャュョワ'
);
-- 実行例
select translate('Az#アワ',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890 !”#$%&()ー-=^~|@‘「[{;+:*」]}、,<。.>・/?_アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォヵヶッャュョヮ',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890 !"#$%&()ー-=^~|@`「[{;+:*」]}、,<。.>・/?_アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォカケッャュョワ'
);
-- "Az#アワ"
半角から全角へ変換する自作関数です。
自作関数を使わずSQLで変換する方法は一番下に記載しています。
引数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文です
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がPC上のどこにあるか確認します。
特に指定なくインストールした場合は、画面の箇所にあると思います。
①のpg_admin.exeを確認したら、そのフォルダ(②)をコピーしておきます。
次にコマンドプロンプトを起動します。
キーボード左したのWindowsボタンを押しながら「R」を押して下さい。
下の画面が起動するので①に「cmd」と入力し②のOKボタンを押します。
上のコマンドを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(日付・日付時刻, 書式)
-- 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
-- 「20190401」を、「YYYY/MM/DD」形式へ変換する
select left('20190401',4)
|| '/' || substring('20190401',5,2)
|| '/' || right('20190401',2) ;
-- 2019/04/01
数値 → 日付に変換する例です。
数値→日付へ直接変換するとエラーとなるため、数値→文字→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(空でない)