【PostgreSQL】ストアド ～ 入門編 ～

PostgreSQL

■ はじめに

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

目次

【１】手順
【２】構文
【３】ストアドを作成する
　例１
　例２
　例３：ユーザ定義型を利用する
【４】ストアドを削除する

【１】手順

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

【２】構文

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

説明 

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

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

注意 

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

例１

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;

例２

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 行検索しました

例３：ユーザ定義型を利用する

-- ユーザ定義型
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;

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

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
ストアド ～ UPDATE or INSERT を行う ～
https://dk521123.hatenablog.com/entry/2014/09/01/224134
ストアド ～ INSERT / UPDATEを行い、それ以外はDELETE ～
https://dk521123.hatenablog.com/entry/2014/09/03/235806
ユーザ定義型
https://dk521123.hatenablog.com/entry/2014/04/23/001200