获取 PostgreSQL 数据库之 sequence 名称列表的 SQL
Thursday, 13. March 2008, 02:26:09
cake/libs/model/dbo/dbo_postgres.php
中,有一句 SQL:
SELECT sequence_name FROM information_schema.sequences
在 PostgreSQL 8 中,以上 SQL 运行出错。
根据原意修改如下:
SELECT c.relname as sequence_name FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE c.relnamespace = n.oid AND c.relkind='S' and n.nspname = 'public'
其中的 'public' 是 schema 名,需要根据实际情况修改。
顺便把 dbo_postgres.php 中包含以上 SQL 的函数修改如下:
function sequenceExists($seq) {
$cache = parent::__cacheDescription('sequences');
if($cache != null) {
return in_array($seq, $cache);
}
$sequences = array();
// ★修改这句
//$res = $this->rawQuery("SELECT sequence_name FROM information_schema.sequences");
$schema = $this->config['schema'];
$res = $this->rawQuery("SELECT c.relname as sequence_name FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE c.relnamespace = n.oid AND c.relkind='S' AND n.nspname = '{$schema}'");
while($row = $this->fetchRow($res)) {
$sequences[] = $row[0]['sequence_name'];
}
parent::__cacheDescription('sequences', $sequences);
return in_array($seq, $sequences);
}
需要说明的是,在新版本的 CakePHP 中,已见不到以上函数。







