CakePHPのインストール

仕事でCakePHPを使うことになった。WEBアプリじゃなくてコンソールアプリだが。
とりあえずCentOSにインストールしてみた。

インストール

composerを使ってインストールした。

/var/www/html の下に example-app というフォルダを作成。そのフォルダに公式サイトの応用インストールというページに書いてある以下の composer.json をまるっとコピーして example-app に置いた。

{
    "name": "example-app",
    "require": {
        "cakephp/cakephp": "2.6.*"
    },
    "config": {
        "vendor-dir": "Vendor/"
    }
}

そして、"composer install" を実行。

# composer install
Loading composer repositories with package information
Installing dependencies (including require-dev)
  - Installing cakephp/cakephp (2.6.7)
    Downloading: 100%         

Writing lock file
Generating autoload files
#

これでCakePHP 2.6.7のファイルの配置が完了。ディレクトリ構成は大体以下のようになる。
cakephpの下にさらにcakephp。なんか変な気もするが、こういうものなのか？

example-app/
+-composer.json
+-Vender/
  +-bin/
  +-autoload.php
  +-composer/
  +-cakephp/
    +-cakephp/
      +-app/
        +-Config/
        +-Console/
        +-Controller/
        +-Lib/
        +-Locale/
        +-Model/
        +-Plugin/
        +-Test/
        +-Vender/
        +-View/
        +-tmp/
        +-webroot/
      +-lib/

また、CakePHPはApacheのmod_rewriteを利用するが、これは既に使えるようになっていたので特に何かする必要はなかった。

設定

timezoneの設定

デフォルトのtimzoneを設定。これはCakePHPとか関係なく、PHPをインストールした後すぐにやっておかなければならないことだった。
/etc/php.ini で下記のように設定する。
最初、/etc/httpd/conf.d/php.conf に書き込んだらエラーなって悩んでしまった。このApacheのPHPに関する設定ファイルであって、PHPの設定ファイルではなかったorz

date.timezone = Asia/Tokyo

Security.saltとSecurity.cipherSeedの変更

app/Config/core.php に設定されている Security.salt と Security.cipherSeed を修正する。初期値のままだと初期画面に警告がでる。

app/tmpのパーミッション変更

app/tmpディレクトリ以下に一時ファイルが作られるので、パーミッションの設定をする。

chmod -R 777 app/tmp

DB接続の設定

DB接続の設定は app/Config の datababase.php.default を database.php にリネームして行う。デフォルトではMySQL用。PostgreSQLを使う場合は datasource を書き換え、port と schema を加える。encoding はコメントになっていたので有効にする。host、login、password、database は自分の環境に合わせて変更する。

class DATABASE_CONFIG {

	public $default = array(
		'datasource' => 'Database/Postgres',
		'persistent' => false,
		'host' => '192.168.1.11',
		'port' => '5432',
		'login' => 'user',
		'password' => 'pass',
		'database' => 'mydb',
		'schema' => 'public',
		'prefix' => '',
		'encoding' => 'utf8',
	);
…

バーチャルホストの設定、SELinuxの無効化、Apacheの再起動

「Laravelのインストール」を app/webroot をドキュメントルートとしてバーチャルホストの設定し、SELinuxを無効化して、Apacheを再起動。

ここまでやって、設定したバーチャルホストにアクセスすると、CakePHPのデフォルトのページが表示される。
＃まだ「DebugKitがインストールされていない」というWarningが出ているが。

参考ページ：
応用インストール — CakePHP Cookbook 2.x ドキュメント
CakePHPのダウンロードとインストール - CakePHPの使い方
AWS EC2にPHP5.5環境でCakePHPを導入 - Qiita
インストール — CakePHP Cookbook 2.x ドキュメント
CakePHPでPostgreSQLを使う - Qiita

参考ページ：Composer
Composerを使ってPHPのパッケージを簡単インストール （2/3）：CodeZine

PHPからPDOでPostgreSQLに接続する

以下のコードでPHPからPDOでPostgreSQLに接続できること確認しようと思った。

$dsn = 'pgsql:dbname=mydb;host=192.168.1.11;port=5432';
$user = 'user';
$pass = 'pass';

try {
    $dbh = new PDO($dsn, $user, $pass);
    $sql = 'SELECT CURRENT_TIMESTAMP';
    foreach ($dbh->query($sql) as $row) {
        print $row[0] . "\n";
    }
    $dbh = null;
} catch (PDOException $e){
    print('[ERROR] ' . $e->getMessage() . "\n");
    die();
}

繋がらない。以下のメッセージが出た。

[ERROR] could not find driver 

"php -i" で確認。
＃"php -i" は phpinfo() を出力するそうだ。

# php -i | grep pdo
/etc/php.d/pdo.ini,
/etc/php.d/pdo_sqlite.ini,
PHP Warning:  《省略》
pdo_sqlite
#

pdo_postgresql とかPostgreSQLと関連ありそうなものが見当たらない。これが「ドライバない」ということか。
"yum list" でパッケージを探すとphp-pgsqlというのがあったのでそれをインストールしてみた。

# yum --enablerepo=remi --enablerepo=remi-php55 list | grep pgsql
apr-util-pgsql.x86_64                     1.3.9-3.el6_0.1             base      
dovecot-pgsql.x86_64                      1:2.0.9-8.el6_6.4           updates   
libdbi-dbd-pgsql.x86_64                   0.8.3-5.1.el6               base      
mod_auth_pgsql.x86_64                     2.0.3-10.1.el6              base      
php-pear-MDB2-Driver-pgsql.noarch         1.5.0-0.7.b4.el6.remi       remi      
php-pgsql.x86_64                          5.5.25-1.el6.remi           remi-php55
php54-php-pgsql.x86_64                    5.4.41-1.el6.remi           remi      
php55-php-pgsql.x86_64                    5.5.25-1.el6.remi           remi      
php56-php-pgsql.x86_64                    5.6.9-1.el6.remi            remi      
rsyslog-pgsql.x86_64                      5.8.10-10.el6_6             updates   
rsyslog7-pgsql.x86_64                     7.4.10-3.el6_6              updates   
uuid-pgsql.x86_64                         1.6.1-10.el6                base      
# yum --enablerepo=remi --enablerepo=remi-php55 install php-pgsql
《省略》
インストール:
  php-pgsql.x86_64 0:5.5.25-1.el6.remi                                          

依存性を更新しました:
  php.x86_64 0:5.5.25-1.el6.remi                                                
  php-cli.x86_64 0:5.5.25-1.el6.remi                                            
  php-common.x86_64 0:5.5.25-1.el6.remi                                         
  php-gd.x86_64 0:5.5.25-1.el6.remi                                             
  php-mbstring.x86_64 0:5.5.25-1.el6.remi                                       
  php-mcrypt.x86_64 0:5.5.25-1.el6.remi                                         
  php-pdo.x86_64 0:5.5.25-1.el6.remi                                            
  php-process.x86_64 0:5.5.25-1.el6.remi                                        
  php-xml.x86_64 0:5.5.25-1.el6.remi                                            

完了しました!
# 

インストール完了。PHP関連が一緒に色々アップデートされた。それから httpd を再起動。
"php -i" を見ると、pdo_pgsqlが入っている。
ちなみに、PHPの拡張モジュールの共有ライブラリ自体のディレクトリは
/usr/lib64/php/modules
設定ファイルのディレクトリは
/etc/php.d

これで最初のプログラムを実行すると、ちゃんと接続できてクエリの結果が返ってきた。

参考ページ：
PHP/PDOを使ってPostgreSQLに接続する - 調べる.db
PHP: PDO - Manual
PHP: PostgreSQL (PDO) - Manual

PostgreSQLのインストール その2

関連記事：
PostgreSQLのインストール その1

外部から接続できるようにする

前の記事で同じサーバ上でpsqlからPostgreSQLサーバに接続できたが、このままでは他のマシンからは接続できない。
これを接続でいるようにする。
まず、サーバが他のマシンからの接続を受け入れるように /var/lib/pgsql/data ディレクトリにある postgresql.conf の listen_address を有効にして以下のように設定する。

listen_address = '*'

また、接続の詳細な設定を同じディレクトリの pg_hba.conf に以下を追記する。

host    all         all         192.168.3.0/24        password

この設定は左から接続の種類、DB、ユーザ、IPアドレス、認証方式を示す。
上記の設定の場合、次の意味になる。
・host：TCP/IP接続
・all：すべてのDB
・all：すべてのユーザー
・192.168.3.0/24：ネットワークアドレス
・password：平文のパスワードで認証する

postgresql.conf と pg_hba.conf を上記のように変更したらサーバを再起動する。
＃reloadでもいけるだろか？

認証方法をパスワードによる認証を設定したのでユーザにパスワードを設定する必要がある。同じマシン上からならpsqlでサーバに接続できるので、以下のようにPostgreSQLのユーザにパスワードを設定する。

# su - postgres
$ psql mydb
psql (8.4.20)
"help" でヘルプを表示します.

mydb=# alter role pguser with password 'pguser';
ALTER ROLE
mydb=# \q

PostgreSQLの設定の他に、iptablesの設定を変更してPostgreSQLが使う5432番ポートを開ける必要がある。
/etc/sysconfig/iptables に以下を追加し、serviceコマンドでiptablesを再起動する。

-A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT

これでホストOS（Windows7）のpsqlやpgadmin IIIからゲストOS（CentOS 6.6）上のPostgreSQLに接続することができる。

参考ページ：
他ホストから接続するための設定
Stray Penguin - Linux Memo (PostgreSQL)
pg_hba.confファイル - 設定ファイル - PostgreSQLの使い方
ユーザにパスワードを設定する - ワレココニイル。
PHP CentOSにおいてPHPからPostgreSQLを操作できるようにする | WEB作業メモ
postgresqlを他ホストからもつなげるようにする - Qiita
コピペから脱出！iptablesの仕組みを理解して環境に合わせた設定をしよう | OXY NOTES

PostgreSQLのインストール その1

関連記事：
yumコマンド
PostgreSQLのコマンドラインクライアントpsql

インストール

PHP 5.3.3 のインストールを参考に、パッケージグループでインストールしてみた。
まず、パッケージグループを調べる。

# yum -v grouplist | grep postgres
   PostgreSQL データベースサーバー (postgresql)
   PostgreSQL データベース接続クライアント (postgresql-client)
# yum groupinfo postgresql
読み込んだプラグイン:downloadonly, fastestmirror, refresh-packagekit
グループ処理の設定をしています
Loading mirror speeds from cached hostfile
 * base: www.ftp.ne.jp
 * extras: www.ftp.ne.jp
 * updates: www.ftp.ne.jp

グループ: PostgreSQL データベースサーバー
 説明: PostgreSQL データベースサーバーと関連パッケージ
 強制的なパッケージ:
   postgresql-server
 標準パッケージ:
   postgresql-docs
 オプション パッケージ:
   postgresql-contrib
   postgresql-plperl
   postgresql-plpython
   postgresql-pltcl
   postgresql-test
   rhdb-utils

とりあえずサーバの方のパッケージグループをインストール。

# yum -y groupinstall postgresql
＜中略＞
依存性を解決しました

================================================================================
 パッケージ               アーキテクチャ
                                        バージョン            リポジトリー
                                                                           容量
================================================================================
インストールしています:
 postgresql-docs          x86_64        8.4.20-1.el6_5        base        6.9 M
 postgresql-server        x86_64        8.4.20-1.el6_5        base        3.4 M
依存性関連でのインストールをします。:
 postgresql               x86_64        8.4.20-1.el6_5        base        2.6 M
 postgresql-libs          x86_64        8.4.20-1.el6_5        base        201 k
＜中略＞
インストール:
  postgresql-docs.x86_64 0:8.4.20-1.el6_5                                       
  postgresql-server.x86_64 0:8.4.20-1.el6_5                                     

依存性関連をインストールしました:
  postgresql.x86_64 0:8.4.20-1.el6_5   postgresql-libs.x86_64 0:8.4.20-1.el6_5  

完了しました!
# id postgres
uid=26(postgres) gid=26(postgres) 所属グループ=26(postgres)
# which psql
/usr/bin/psql

依存関係からサーバだけでなくクライアント(psql)もインストールされた。
また、postgres というユーザとグループが作成されていた。

サービスの起動

参考ページの「3. PostgreSQL を使ってみる」をやってみた。

まず、Linuxのアカウントを新たに１つ作る。
＃意味は考えずに、とりあえず作った。

# useradd pguser

次にサービスを起動しようとしたが、失敗。

# service postgresql start

/var/lib/pgsql/data is missing. Use "service postgresql initdb" to initialize the cluster first.
                                                           [失敗]

エラーメッセージの中に書かれている "service postgresql initdb" を実行してみる。すると、空だった /var/lib/pgsql/data/ ディレクトリの中に設定ファイルなどができていた。
そしてサービスの起動を試みると、今度は成功。

# service postgresql initdb
データベースを初期化中:                                    [  OK  ]
# ls /var/lib/pgsql/
backups  data  pgstartup.log
# ls /var/lib/pgsql/data/
PG_VERSION  pg_clog        pg_log        pg_subtrans  pg_xlog
base        pg_hba.conf    pg_multixact  pg_tblspc    postgresql.conf
global      pg_ident.conf  pg_stat_tmp   pg_twophase
# cat /var/lib/pgsql/data/PG_VERSION 
8.4
# service postgresql start
postgresql サービスを開始中:                               [  OK  ]

ロールとDBの作成

Linuxのアカウントを postgres に切り替えて、"psql -l" でDBを確認。初期状態で postgres, template0, template1 の3つのDBがある。
エンコーディングはデフォルトでUTF-8なんだな。SJISじゃなくてよかった。

# su - postgres
$ psql -l
                                         データベース一覧
   名前    |  所有者  | エンコーディング |  照合順序   | Ctype(変換演算子) |    
  アクセス権       
-----------+----------+------------------+-------------+-------------------+-----------------------
 postgres  | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | 
 template0 | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | =c/postgres
                                                                           : postgres=CTc/postgres
 template1 | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | =c/postgres
                                                                           : postgres=CTc/postgres
(3 行)

createuserコマンドでPostgreSQLのロールを作成する。ロールとはユーザーだと思えばいいらしい。
ロールの名前は先ほど作ったLinuxのアカウントと同じ pguser として、データベース作成権限を付与。

$ createuser pguser
新しいロールをスーパーユーザとしますか?  (y/n)n
新しいロールにデータベース作成権限を与えますか? (y/n)y
新しいロールにロールを作成する権限を与えますか? (y/n)n

Linuxのアカウントを pguser に切り替えて、createdbコマンドでDBを新規作成。アカウントを切り替えるのはDBの所有者を pguser にするため。
"psql -l" でDBを確認すると、所有者が pguser の新しいDB mydb ができていた。

$ exit
logout
# su - pguser
$ createdb mydb
$ psql -l
                                         データベース一覧
   名前    |  所有者  | エンコーディング |  照合順序   | Ctype(変換演算子) |    
  アクセス権       
-----------+----------+------------------+-------------+-------------------+-----------------------
 mydb      | pguser   | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | 
 postgres  | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | 
 template0 | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | =c/postgres
                                                                           : postgres=CTc/postgres
 template1 | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       | =c/postgres
                                                                           : postgres=CTc/postgres
(4 行)

pguserのままで psql から mydb に接続できた。アカウントが postgres でも接続できたが、root できなかった。

$ psql mydb
psql (8.4.20)
"help" でヘルプを表示します.

mydb=> \du
                ロール一覧
 ロール名 |        属性        | メンバー 
----------+--------------------+----------
 pguser   | DBを作成できる     | {}
 postgres | スーパーユーザ     | {}
          : ロールを作成できる   
          : DBを作成できる       

今回、ロールを作る前に同じ名前のユーザーアカウントをLinuxに作った。こういうのをIdent認証というらしい。
Linuxのアカウントと関係なくロールを作る方法もあると思うので、いずれ調べたい。

ログファイルの設定

ログファイルはデフォルトで /var/lib/pgsql/data/pg_log ディレクトリに
postgresql-＜3文字の曜日＞.log
という形式のファイル名に記録される。曜日はPostgreSQLを起動した曜日。また、ログの内容にその事象が発生した日時が記載されない。
＃ファイル名といい日時が記載されないことといい、変なデフォルト設定だな。
日時はあったほうが良さそうなので、postgresql.conf を修正。
log_line_prefix という項目が # でコメントアウトされているので、# を削除し、下記のように書き換えた。
それから"service postgresql reload" で設定を再読み込みすると、ログに日時が記録されるようになる。

log_line_prefix = '%t %d[%p-%l]'

参考ページ：
CentOS で PostgreSQL を使ってみよう！(1) － Let's Postgres

列の値を使って日時に「+n日」する

テーブルtblにtimestamp型の列dtと文字列型の列nがある。nは文字列型だけど入ってる値は"1"とか"12"のような数字のみからなる文字列。
nの値を使って「dt＋n日」という計算をしたい。例えばdtの値が "2015-01-01 12:30:45" でnの値が "3" ならば
"2015-01-01 12:30:45"＋3日で "2015-01-04 12:30:45" という結果が欲しい。
最初、以下のようにしたが構文エラーになった。

SELECT dt + interval (n || ' days') FROM tbl

次のようにしたら欲しい結果が得られた。

SELECT dt + CAST(n || ' days' AS interval) FROM tbl

もしnが整数型ならこうかな。

SELECT dt + CAST(TO_CHAR(n, 'FM9999') || ' days' AS interval) FROM tbl

参考ページ：
Postgresでの時間加算select current_timestamp + interval '1 hours';... - Yahoo!知恵袋

PL/pgSQL その2

関連記事：PL/pgSQL

PL/pgSQLで書いたコードの実行

以前の記事ではPL/pgSQLの使いみちと使い方を書いていなかった。PL/pgSQLは関数の定義に使う。実行は関数として実行する。
関数はSELECT文で実行する。下記のように、FROMがなくてもエラーにならない。

SELECT ＜関数名＞

OracleのPL/SQLはコードをファイルに保存しファイルを指定して実行することができたと思うが、PL/pgSQLはそういうことができない。あくまで関数を記述して関数として実行するしかない。その点はPL/SQLより不便な気がする。

データを変更するPL/pgSQLコードの例

以前の記事ではデータを検索する例だけだったので、データを変更するコードを示す。
下記の例では、table1 を条件を指定して検索し、その検索結果の user_id を area_id=5 で table2 に挿入している。戻り値は挿入した件数である。

CREATE OR REPLACE FUNCTION insert_table2()
RETURNS int AS '
DECLARE
  cur CURSOR FOR 
    SELECT user_id 
    FROM table1 
    WHERE flag_delete IS NULL type = 1
      AND user_id NOT IN (SELECT user_id FROM table2 WHERE area_id = 5) 
    ORDER BY user_id;
  r table1;
  count int;
BEGIN
  count := 0;
  OPEN cur;
  LOOP
    FETCH cur INTO r;
    IF NOT FOUND THEN
      EXIT;
    END IF;
    INSERT INTO table2 (user_id, area_id, create_date) 
    VALUES (r.user_id, 5, CURRENT_TIMESTAMP);
    count := count + 1;
  END LOOP;
  CLOSE cur;
  RETURN count;
END
' LANGUAGE plpgsql;

17～19行目で最後までフェッチしたら終了するようにしている。この部分がないと無限ループになる可能性がある。
＃この関数の場合はINSERT文でSQLエラーになり止まる。

PostgreSQLの文字列のエスケープ

SQLの文字列リテラルはシングルクォートで囲われた文字の並びである。文字列リテラルの中にシングルクォートを入れたい場合、PostgreSQLではシングルクォートを続けて2つ書くことで文字列リテラル中にシングルクォートを記述できる。

dbtest=> SELECT 'ab''c';
 ?column?
----------
 ab'c
(1 行)

また、文字列リテラルを記述するときの最初のシングルクォートの前に "E" を書くことによって、C言語の文字列リテラルのようにバックスラッシュでエスケープ文字として使用できる。シングルクォートは "\'"、バックスラッシュ文字自体を "\\" と記述できる。また、"\t", "\n" などの制御文字も記述できる。

dbtest=> SELECT E'a\'b\\c\td\nf';
 ?column?
-----------
 a'b\c   d+
 f
(1 行)

参考サイト：
PostgreSQL 9.1.5文書 語彙の構成
PostgreSQLで「'」（シングルクォーテーション／単一引用符）をエスケープ処理する方法 - r_nobuホームページ
PostgreSQLでエスケープ文字を利用する場合の注意 - takami_hirokiの日記

TIMESTAMP WITH TIME ZONE型

既存のWEBアプリケーションに対して国際化対応というか現地時間対応みたいなことをする可能性が浮上してきた。国際化なんて全く考慮してなくて、テーブルの日時のカラムは TIMESTAMP WITHOUT TIME ZONE 型だ。
TIMESTAMP WITHOUT TIME ZONE 型（以下、TZなしと記述） を TIMESTAMP WITH TIME ZONE 型（以下、TZありと記述）にキャストしたらどうなるか、やってみたメモ。
キャストの書き方は、標準SQLで書くと長いのでPostgreSQLの方言で記述。

dbtest=> \d t_test
              テーブル "public.t_test"
  カラム   |             型              |  修飾語
-----------+-----------------------------+----------
 test_id   | numeric                     | not null
 contents  | character varying(20)       |
 test_date | timestamp without time zone |
インデックス:
    "t_test_pkey" PRIMARY KEY, btree (test_id)

dbtest=> SHOW timezone;
  TimeZone
------------
 Asia/Tokyo
(1 行)

dbtest=> SELECT * FROM t_test;
 test_id | contents |      test_date
---------+----------+---------------------
       1 | abcde    | 2014-07-14 07:00:00
(1 行)

dbtest=> SELECT test_date::timestamptz FROM t_test;
       test_date
------------------------
 2014-07-14 07:00:00+09
(1 行)

dbtest=> SELECT test_id FROM t_test WHERE test_date::timestamptz = '2014-07-13 22:00:00+0000'::timestamptz;
 test_id
---------
       1
(1 行)

dbtest=> SELECT test_id FROM t_test WHERE test_date::timestamptz = '2014-07-13 15:00:00-0700'::timestamptz;
 test_id
---------
       1
(1 行)

どうやらTZなしをTZありにキャストするとDBに設定されたタイムゾーンの時間となるらしい。
上記の例なら、TZなしの 2014/07/14 07:00 をTZありにキャストすると

2014/07/14 07:00+0900(JST)
= 2014/07/13 22:00+0000(UTC)
= 2014/07/13 15:00-0700(PDT)

という風に変換される。
＃上に書いた3つの時間はすべて同じ時間。表現の仕方はタイムゾーンの数だけある。

WEBアプリケーションの現地時間対応をもしするとしたら、テーブルのTZなしのカラムをTZありに変更し、合わせてコードも修正するのがまともなやり方なんだろう。
でもそんな工数は取れないだろうから、SQL文のいろんなところにキャストを入れる「なんちゃって国際化」になりそうな気がする。

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で自作の関数を定義する

PostgreSQLの関数は CREATE FUNCTION で定義する。
以下、例を示す。例2、例3については説明どおりに動くか自信はないが^^;

例1：加算
IMMUTABLE はデータベースに変更をしないことを示す。
RETURNS NULL ON NULL INPUT は引数にNULLがある場合NULLを返すことを示す。

CREATE FUNCTION add(integer, integer) RETURNS integer
    AS 'select $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

例2：指定された日付の月の日数を返す関数
double precision は倍精度浮動小数点。extract() の返り値がこの型なのでこの関数の返り値の型も double precision にしている。
処理の部分はBEGIN～END で処理を記述している。これはSQLではなくPL/pgSQLなので、LANGUAGEは plpgsql としている。
PL/pgSQLについては次の記事を参照。

CREATE OR REPLACE FUNCTION get_month_last_day(date) RETURNS double precision AS '
BEGIN
  RETURN extract(DAY FROM date_trunc(''month'', $1 + interval ''1 month'') - interval ''1 day'');
END;
' LANGUAGE plpgsql;

例3：指定された日付が月の最終日かどうかを返す関数

CREATE OR REPLACE FUNCTION is_month_last_day(date) RETURNS boolean AS '
BEGIN
  RETURN date_trunc(''day'', $1) = date_trunc(''month'', $1 + interval ''1 month'') - interval ''1 day'';
END;
' LANGUAGE plpgsql;

参考サイト： PostgreSQL 8.4.4文書 Ⅳ.リファレンス Ⅰ.SQLコマンド CREATE FUNCTION