CodeIgniter2.1 数据库添加支持postgres包含RETURNING查询的取值
本帖最后由 mark35 于 2013-3-2 19:01 编辑postgreSQL支持 INSERT|UPDATE|DELETE查询时返回指定栏目值，比如
INSERT INTO user (id, name) VALUES (DEFAULT, 'new user') RETURNING id;
INSERT INTO user (id, name) VALUES (DEFAULT, 'new user') RETURNING *;
对以上任意一条SQL查询的结果对象进行取值就可以获得需要的值，
$last_inset_id = $this->db->query($sql)->row()->id;
or
$res = $this->db->query($sql)->row_array();
$last_insert_id = $res ? $res['id'] ? 0;
但CI2默认数据库驱动如果判断是写入的语句那么返回的是bool值而不是db结果集对象。修改如下：
system/databaseDB_driver.php
添加函数
// --------------------------------------------------------------------
/**
* Determines if a query is a "write" type with returning colums values (postgres)
* @author waiting@xiaozhong.biz
*
* @access public
* @param string An SQL query string
* @return boolean
*/
function is_write_return_type($sql)
{
if ( ! preg_match('/^\s*"?(INSERT|UPDATE|DELETE)\s+?/i', $sql))
{
return FALSE;
}
return (bool) preg_match('/\sRETURNING\b.+?/i', $sql);
}
然后此文件中约351行处代码找到
if ($this->is_write_type($sql) === TRUE)
{
// If caching is enabled we'll auto-cleanup any
// existing files related to this particular URI
if ($this->cache_on == TRUE AND $this->cache_autodel == TRUE AND $this->_cache_init())
{
$this->CACHE->delete();
}
return TRUE;
}
修改为
if ($this->is_write_type($sql) === TRUE)
{
// If caching is enabled we'll auto-cleanup any
// existing files related to this particular URI
if ($this->cache_on == TRUE AND $this->cache_autodel == TRUE AND $this->_cache_init())
{
$this->CACHE->delete();
}
// waiting ADD
if ( ! $this->is_write_return_type($sql))
{
return TRUE;
} // ADD END
}
注：通过 RETURNING PK 方式返回值这才是正确的、严谨的获得 last insert id的方法。CI定义的 $this-db->insert_id() 方法是根据垃圾mysql的方式处理得来的
首发 http://xiaozhong.biz/thread-312-1-1.html
页:
[1]