PL/pgSQL
PL/pgSQLはPostgreSQLで使える手続き型言語である。OracleにおけるPL/SQLに当たる。PL/pgSQLはPL/SQLを参考に実装したらしい。
PL/pgSQLの構造は下記のようなブロック構造になっている。
[ <<label>> ] [ DECLARE declarations ] BEGIN statements END [ label ];
コメントは2種類ある
・"--" からその行の終わりまで。
・"/*" で始まり "*/" で終わる。
例1:2012/01/01~2013/01/31までの日付をINSERTする。値は何も返さない。
CREATE OR REPLACE FUNCTION test_func_01() RETURNS void AS ' DECLARE tmp date; BEGIN tmp := ''2012-01-01''::date; FOR i IN 1..731 LOOP INSERT INTO t_result (id, type, date) VALUES (i, ''A'', tmp); tmp := tmp + interval ''1 day''; END LOOP; END ' LANGUAGE plpgsql;実行結果
dbtest=> SELECT test_func_01(); test_func_01 -------------- (1 行) dbtest=> SELECT * FROM t_result ORDER BY id; id | type | date -----+------+--------------------- 1 | A | 2012-01-01 00:00:00 2 | A | 2012-01-02 00:00:00 … 730 | A | 2013-12-30 00:00:00 731 | A | 2013-12-31 00:00:00 (731 行) dbtest=>
例2:SELECTを3回実行し、その結果をまとめて返す。
CREATE OR REPLACE FUNCTION test_func_02(OUT a integer, OUT b char(1), OUT c timestamp) RETURNS SETOF RECORD AS ' BEGIN FOR i IN 1..3 LOOP SELECT * from t_result WHERE id = i INTO a, b, c; RETURN NEXT; END LOOP; RETURN; END ' LANGUAGE plpgsql;実行結果
dbtest=> SELECT * FROM test_func_02(); a | b | c ---+---+--------------------- 1 | A | 2012-01-01 00:00:00 2 | A | 2012-01-02 00:00:00 3 | A | 2012-01-03 00:00:00 (3 行) dbtest=>
例3:カーソルを使う。
CREATE OR REPLACE FUNCTION test_func_03(OUT a timestamp, OUT dummy integer) RETURNS SETOF RECORD AS ' DECLARE cur CURSOR FOR SELECT * FROM t_result ORDER BY id; r t_result; BEGIN OPEN cur; LOOP FETCH cur INTO r; SELECT r.date INTO a; RETURN NEXT; IF r.id = 3 THEN EXIT; END IF; END LOOP; RETURN; END ' LANGUAGE plpgsql;実行結果
dbtest=> SELECT * FROM test_func_03(); a | dummy ---------------------+------- 2012-01-01 00:00:00 | 2012-01-02 00:00:00 | 2012-01-03 00:00:00 | (3 行) dbtest=>
複数のクエリーの結果を返す場合で、よくわからないな現象があった。
2列を返す下記の関数は特に問題なく定義できて実行もできた。
CREATE OR REPLACE FUNCTION test1(OUT a integer, OUT b integer) RETURNS SETOF RECORD AS ' BEGIN FOR i IN 1..10 LOOP SELECT i, i+1 INTO a, b; RETURN NEXT; END LOOP; RETURN; END ' LANGUAGE plpgsql;
しかし、返す値を1列にすると定義自体がエラーになる。
CREATE OR REPLACE FUNCTION test2(OUT a integer) RETURNS SETOF RECORD AS ' BEGIN FOR i IN 1..10 LOOP SELECT i INTO a; RETURN NEXT; END LOOP; RETURN; END ' LANGUAGE plpgsql;
エラーメッセージは以下。
ERROR: OUTパラメータのため、関数の戻り値型はintegerでなければなりません。
"SELECT i" の i に "::integer" と付けても同じエラー。意味がわからない。
例3のOUTパラメータdummyはこのエラーを避けるために付けた。
参考サイト:
PostgreSQL 8.4.4文書 第38章 PL/pgSQL - SQL手続き言語
複数行複数列を返すPL/pgSQL関数 - iakioの日記 - postgresqlグループ
PostgreSQL初心者の悪戦苦闘ぶりをメモする pl/pgsql篇 | 読書と技術となんか色々のログ - 楽天ブログ
« PostgreSQLで自作の関数を定義する | Main | アップロードファイルによるサーバ側スクリプト実行 »
「PostgreSQL」カテゴリの記事
- CakePHPのインストール(2015.06.14)
- PHPからPDOでPostgreSQLに接続する(2015.06.09)
- PostgreSQLのインストール その2(2015.03.29)
- PostgreSQLのインストール その1(2015.03.23)
- 列の値を使って日時に「+n日」する(2015.02.04)
「SQL」カテゴリの記事
- 列の値を使って日時に「+n日」する(2015.02.04)
- TIMESTAMP WITH TIME ZONE型(2014.07.20)
- PL/pgSQL(2014.02.16)
- PostgreSQLで自作の関数を定義する(2014.02.15)
- PostgreSQLのPREPARE(2014.02.15)
The comments to this entry are closed.
Comments