PostgreSQLスキルアップノート（自己啓発のための個人サイト）
SQL関数
【一覧に戻る】
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
■■■■ PostgreSQL スキルアップノート
■■■■
■◆■■ ＳＱＬ関数
■■■■
■■■■
■■■■ 2012/12/22
■■■■ 使用環境：PostgreSQL9.1.4 (CentOS6.2)
(C) 2012 ohdb
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
★自己都合によりマニュアルへのリンクは9.2としています。ご了承下さい。
★このページはそのままカット&ペーストで実行して試すことができます。
【マニュアル】
問い合わせ言語（SQL）関数
→●[マニュアル]
CREATE FUNCTION
→●[マニュアル]
【参考記事】
■■ ＳＱＬ関数とは？
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
ＳＱＬ関数とはＳＱＬによるユーザ定義関数。
正式名は「問い合わせ言語（ＳＱＬ）関数」
機能は限られるが、シンプルで扱いやすく、用途によってはかなり利用価値があると思う。
【参考】ユーザ定義関数は、SQL関数以外も合わせて以下の４種類ある。
・問い合わせ言語(SQL)関数 ・・・SQLで作成された関数
・手続型言語関数 ・・・PL/pgSQLやPL/Tclなどで作成された関数
・内部関数 ・・・Cで作成されたPostgreSQLサーバに静的にリンクされた関数
・C言語関数 ・・・Cで作成された動的ロードされる関数
本ページは「問い合わせ言語(SQL)関数」が対象。
以下「SQL関数と呼ぶ。
【注意】関数とSQL関数
PostgreSQLからあらかじめ提供されている関数（例：sumなど）はあくまでも「関数」であって
「SQL関数」とは呼ばない。
■■ テスト用テーブル作成
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
【□】 テーブル作成
drop table if exists t1;
create table t1 (c1 serial primary key,c2 int,c3 text);
insert into t1 values
(default,1000,'ABC'),
(default,2000,'DEF'),
(default,3000,'GHI'),
(default,4000,'JKL'),
(default,5000,'MNO');
select * from t1;
c1 | c2 | c3
----+------+-----
1 | 1000 | ABC
2 | 2000 | DEF
3 | 3000 | GHI
4 | 4000 | JKL
5 | 5000 | MNO
(5 rows)
■１■ 引数なし・戻り値１個
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
引数なし・戻り値１個（または戻り値なし）のＳＱＬ関数の作り方。
基本
CREATE FUNCTION SQL関数名() RETURNS 戻り値の型 AS $$
SELECT 文など;
$$ LANGUAGE SQL;
（戻り値なしの場合はRETURNSに void と指定）
本項の書き方では、以下の条件あり
・関数の中に記述するSELECT文は１個のカラムを表示するように作成する必要がある。
複数カラムのSELECTは不可。
・結果が複数行返るようなSELECT文でも問題ないが、
最初に出力された１件だけが戻り値の対象になる
・SQL文を複数記述することは不可能ではないが最後に実行したSQLの
結果が戻り値になる。
（SELECTやRETURNING句を持つINSERT,UPDATE,DELETE）
・戻り値の型は例のようにデータ型を指定する他、
returns t1.c3%TYPE のようにテーブルの型を参照することも可能。
【□】例１ 基本
drop function fnc_test11();
create or replace function fnc_test11() returns int AS $$
SELECT min(c2) from t1;
$$ LANGUAGE SQL;
select fnc_test11();
fnc_test11
-----------
1000
【□】例２ 複数の文は最後の文の結果が返る
drop function fnc_test12();
create or replace function fnc_test12() returns intr AS $$
SELECT max(c2) from t1; -- こちらは戻り値にはならない
SELECT min(c2) from t1; -- こちらが戻り値の対象
$$ LANGUAGE SQL;
select fnc_test12();
fnc_test12
-----------
1000
【□】例３ 複数行は最初の行が返る
drop function fnc_test13();
create or replace function fnc_test13() returns varchar AS $$
SELECT c3 from t1 order by c1;
$$ LANGUAGE SQL;
select fnc_test13();
fnc_test13
-----------
ABC
・・ SELECT c3 from t1 order by c1の結果は５行あるが最初の１件が返る。
【□】例４ 何も結果を返さない(void)
drop function fnc_test14();
create or replace function fnc_test14() returns void AS $$
UPDATE t1 set c3='aaaaaaa' where c1=1;
$$ LANGUAGE SQL;
select fnc_test14();
fnc_test14
-----------
■２■ 複数行の戻り値
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
１．SETOFを使う方法
──────────────────────────────
複数行を返すようにするにはreturnsにsetofを追加。
【□】 SQL関数を作成
drop function fnc_test21();
create or replace function fnc_test21() returns setof varchar AS $$
SELECT c3 from t1 order by c1;
$$ LANGUAGE SQL;
select fnc_test21();
fnc_test21
------------
ABC
DEF
GHI
JKL
MNO
(5 rows)
上記は単一の列を複数行戻しているが
戻り値の内容に応じて
SETOF RECORD
SETOF TABLE名
なども可
２．TABLEを使う方法
──────────────────────────────
returnsにsetof（またはsetof records)ではなくTABLEを使う方法。
(ここでのTABLEの意味に注意）
複数の列を戻せるのでOUTパラメータも使わなくても済む。
TABLEで戻す場合は、OUTやINOUTパラメータは使用不可。
標準SQLで規定されており、SETOFよりも移植面ではよい。（？）
【□】 SQL関数を作成
drop function fnc_test22();
create or replace function fnc_test22() returns table(col1 integer,col3 text) AS $$
SELECT c1,c3 from t1 order by c1;
$$ LANGUAGE SQL;
select * from fnc_test22();
col1 | col3
------+------
1 | ABC
2 | DEF
3 | GHI
4 | JKL
5 | MNO
■３■ ｉｎ引数
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
基本
CREATE FUNCTION SQL関数名(引数1の型,引数2の型・・) RETURNS 戻り値の型 AS $$
SELECT 文など;
$$ LANGUAGE SQL;
・関数の中では$1,$2のように番号で値を利用。
（ただし9.2以降は引数に名前を付けられる）
・9.2より引数に名前をつけることが可能
今回省略（詳細はマニュアル参照）
例）CREATE FUNCTION fnc_test(x integer, y integer) RETURNS integer AS $$
SELECT kingaku from table WHERE c1=x and c2=y;
$$ LANGUAGE SQL;
【□】 SQL関数を作成
drop function if exists fnc_test31(int);
create or replace function fnc_test31(int) returns setof int AS $$
SELECT c2 from t1 where c2 >= $1 order by c2;
$$ LANGUAGE SQL;
select fnc_test31(2000);
fnc_test32
------------
2000
3000
4000
5000
参照は以下のようにテーブルと同じように扱うこともできる。
マニュアルの「テーブルソースとしてのSQL関数」の節を参照
select * from fnc_test32(2000);
fnc_test32
------------
2000
3000
4000
5000
select * from t1 join fnc_test32(2000) c2list on t1.c2=c2list;
select * from t1,fnc_test32(2000) c2list where t1.c2=c2list;
c1 | c2 | c3 | c2list
----+------+-----+--------
2 | 2000 | DEF | 2000
3 | 3000 | GHI | 3000
4 | 4000 | JKL | 4000
5 | 5000 | MNO | 5000
■４■ ｏｕｔ引数
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
【□】 SQL関数を作成
drop function fnc_test41(in int,in varchar,out int,out int,out varchar);
CREATE FUNCTION fnc_test41(in int,in varchar,out int,out int,out varchar) returns setof record AS $$
SELECT c1,c2,c3 from t1 where c2=$1 or c3=$2;
$$ LANGUAGE SQL;
SELECT fnc_test41(5000,'ABC') ;
fnc_test41
--------------
(1,1000,ABC)
(5,5000,MNO)
参照は以下のようにテーブルと同じように扱うこともできる。
マニュアルの「テーブルソースとしてのSQL関数」の節を参照
SELECT * FROM fnc_test41(5000,'ABC');
column1 | column2 | column3
---------+---------+---------
1 | 1000 | ABC
5 | 5000 | MNO
SELECT * FROM fnc_test41(5000,'ABC') a where a.column3='ABC';
column1 | column2 | column3
---------+---------+---------
1 | 1000 | ABC
■５■ 戻り値の型としてテーブルを指定する方法
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
【□】 SQL関数を作成
CREATE FUNCTION fnc_test51(int) RETURNS SETOF t1 AS $$
SELECT * FROM t1 WHERE c2 > $1;
$$ LANGUAGE SQL;
SELECT * FROM fnc_test51(3000);
c1 | c2 | c3
----+------+-----
4 | 4000 | JKL
5 | 5000 | MNO
【参考】なぜSETOFの次にテーブル名がくるのか？
SETOF t1
・・・これはテーブル名ではなくテーブル名t1と同名の複合型のデータ型である。
CREATE TABLEする際に、テーブル名と同じ名前のデータ型も自動的に作成される。
・CREATE TABLE→●[マニュアル]
「さらにCREATE TABLEは、作成するテーブルの1行に対応する複合型のデータ型を作成します。」
■６■引数のデフォルト値
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
略
詳細はマニュアル「引数にデフォルト値を持つSQL関数」の節を参照
■７■ ＳＱＬ関数の確認
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
【□】 \df fnc_test*
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+------------+-------------------+---------------------+--------
public | fnc_test11 | integer | | normal
public | fnc_test12 | integer | | normal
public | fnc_test13 | character varying | | normal
public | fnc_test14 | void | | normal
【□】 \df fnc_test2*
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+------------+------------------+-----------------------------------------------------------------------------+-------- public | fnc_test21 | record | integer, character varying, OUT integer, OUT integer, OUT character varying | normal
public | fnc_test22 | integer | | normal
ソースコードも表示するには+を付ける。
【□】 \df+ fnc_test21
List of functions
Schema | Name |・・ | Source code | Description
--------+------------+・・-+------------------------------+-------------
public | fnc_test11 |・・ | +|
| |・・ | SELECT min(c2) from t1;+|
| |・・ | |
以上
