CREATE FUNCTION （配列を返却）
PostgreSQLの良いところの一つに配列を簡単に利用できる点です。システムで提供しているファンクションの中にも普通に返却値を配列にしているものもあります。そのせいか配列を操作する配列関数が充実しておりこの辺も大変便利です。Oracleでもコレクション型をTYPEで定義すれば「配列っぽい事」は実現できるのですが、「配列と聞いて一般的に思い浮かべるイメージ」の事はできません。まさに、以下PostgreSQLで実現できるような事（１カラム内に複数項目を配列として持つ）をOracleでは実現できません。
という所で実際に配列を返却するストアドファンクションのサンプルプログラムが以下です。
サンプルソース
パラメータで都道府県名を指定すれば、存在する区を配列で返却するものです。（補足：以下SECURITY DEFINER を有効にした場合、ストアドプログラムを作成したユーザの権限で動作します。Oracleと異なりPostgreSQLはストアドプログラムを呼び出したユーザの権限で動作するのがデフォルトになっています）
CREATE OR REPLACE FUNCTION sooni.fnc_wardcity(
p_prefectures character varying)
-- RETURNS character varying[]
-- RETURNS text[]
RETURNS varchar[]
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
-- SECURITY DEFINER
AS $BODY$
DECLARE
v_rec record;
i integer;
result_arr varchar[];
cur cursor for select ward from wardcity
where prefectures = p_prefectures;
BEGIN
i := 1;
for v_rec in cur loop
result_arr[i] := v_rec.ward;
-- raise info '[%]: % ',i, v_rec.ward;
i := i + 1;
end loop;
return result_arr;
END;
$BODY$;
実行結果
-- 宮城県をパラメータに実行
--
myposdb=# select fnc_wardcity('宮城県');
fnc_wardcity
--------------------------------------------------------------------
{仙台市太白区,仙台市宮城野区,仙台市泉区,仙台市若林区,仙台市青葉区}
(1 row)
-- 千葉県をパラメータに実行
-- 宮城県とは配列の項目数が異なる事わかります。
--
myposdb=# select fnc_wardcity('千葉県');
fnc_wardcity
---------------------------------------------------------------------------------
{千葉市中央区,千葉市稲毛区,千葉市緑区,千葉市美浜区,千葉市花見川区,千葉市若葉区}
(1 row)
-- array_to_string()を使うと配列型からstring型に変換します。
-- 以下は区切り文字を'/'で指定しています。
--
myposdb=# select array_to_string(fnc_wardcity('千葉県'),'/');
array_to_string
-------------------------------------------------------------------------------
千葉市中央区/千葉市稲毛区/千葉市緑区/千葉市美浜区/千葉市花見川区/千葉市若葉区
(1 row)
-- unnest()を使うと配列から行集合に変換してくれます。
-- 個人的にもよく利用するファンクションです
--
myposdb=# select unnest(fnc_wardcity('広島県'));
unnest
----------------
広島市中区
広島市佐伯区
広島市南区
広島市安佐北区
広島市安佐南区
広島市安芸区
広島市東区
広島市西区
(8 rows)
myposdb=#テストデータ
create table wardcity
(
sid serial,
prefectures varchar(10),
ward varchar(40)
);
insert into wardcity(prefectures,ward) values
('千葉県','千葉市中央区')
, ('千葉県','千葉市稲毛区')
, ('千葉県','千葉市緑区')
, ('千葉県','千葉市美浜区')
, ('千葉県','千葉市花見川区')
, ('千葉県','千葉市若葉区')
, ('宮城県','仙台市太白区')
, ('宮城県','仙台市宮城野区')
, ('宮城県','仙台市泉区')
, ('宮城県','仙台市若林区')
, ('宮城県','仙台市青葉区')
, ('広島県','広島市中区')
, ('広島県','広島市佐伯区')
, ('広島県','広島市南区')
, ('広島県','広島市安佐北区')
, ('広島県','広島市安佐南区')
, ('広島県','広島市安芸区')
, ('広島県','広島市東区')
, ('広島県','広島市西区');
