【PostgreSQL】ストアドファンクション

■ はじめに

今回は、
PostgreSQL の ストアドファンクション (Stored Function) について扱う

目次

【1】手順
【2】構文
【3】ストアドを作成する
【4】ストアドを削除する

【1】手順

[1] pgAdmin IIIを起動する
[2] [SQL]アイコン(任意のSQLクエリーを実行する)を選択する
[3] [SQLエディタ]タブ内にストアドプロシージャを入力
[4] 三角アイコン(クエリーの実行)を選択する

【2】構文

CREATE [OR REPLACE] FUNCTION 【関数名】(【引数】) RETURNS [SETOF] 【戻り値】 AS $$
-- 関数の記述
$$ LANGUAGE 【使用言語】;

説明

 * 「OR REPLACE」を指定すると、既存の関数を上書きする
 * 複数の戻り値を取得する場合は、「SETOF」を指定

【3】ストアドを作成する

注意

* カラム名と同じ引数名にしないこと

例1

CREATE OR REPLACE FUNCTION SampleStoredProcedure(typeValue INTEGER)
RETURNS TABLE(outId char(8), outName varchar(100), outSex char(1)) AS $$
DECLARE
BEGIN
    RETURN QUERY SELECT id, name, sex FROM person;
END;
$$ LANGUAGE PLpgSQL;

例2

CREATE OR REPLACE FUNCTION Sample2StoredProcedure(typeValue INTEGER)
RETURNS TABLE(outId char(8), outName varchar(100), outSex char(1)) AS $$
DECLARE
    sql TEXT;
BEGIN
    CASE typeValue
        WHEN 10 THEN
            RAISE NOTICE 'typeValue = 10.';
            sql := 'SELECT id, name, sex FROM person';
        ELSE
            RAISE NOTICE 'typeValue != 10.';
            sql := 'SELECT id, name, sex FROM person';
    END CASE;
    RETURN QUERY EXECUTE sql;
END;
$$ LANGUAGE PLpgSQL;

使用例

SELECT Sample2StoredProcedure(10);
SELECT Sample2StoredProcedure(11);

実行結果例

データの出力
"(X0000001,Mike,m)"

メッセージ
NOTICE:  typeValue = 10.
NOTICE:  typeValue != 10.
query result with 1 row discarded.

クエリ全体 実行時間:13 ms.
1 行検索しました

例3:ユーザ定義型を利用する

-- ユーザ定義型
CREATE TYPE PersonEntity AS (id char(8),
                       name varchar(100),
                       sex char(1));

CREATE OR REPLACE FUNCTION Sample3StoredProcedure(argId char(8))
RETURNS PersonEntity AS $$
DECLARE
    returnValue RECORD;
BEGIN
    SELECT id, name, sex FROM Person WHERE id = argId
    INTO returnValue;
    RETURN returnValue;
END;
$$ LANGUAGE PLpgSQL;

【4】ストアドを削除する

DROP FUNCTION Sample2StoredProcedure(typeValue INTEGER);

参考文献

http://www.techscore.com/tech/sql/SQL13/13_01.html/
http://shin-hanuman.blogspot.jp/2011/06/postgresql.html
http://foolprogrammer.blogspot.jp/2012/07/postgresqpl-pgsqlreturns-table.html
http://foolprogrammer.blogspot.jp/2012/07/postgresqpl-pgsqlcase-when-else.html

関連記事

ストアドプロシージャ / ファンクション
https://dk521123.hatenablog.com/entry/2010/08/06/112528