[PostgreSQL]在匯入資料後更新 sequences

edited 十月 2013 in 資料庫
剛剛搞定的東西,丟出來當磚頭
$sequences = $dbn->fetchAll('SELECT c.relname FROM pg_class c WHERE c.relkind = \'S\';');
foreach ($sequences AS $sequence) {
    $strlen = strlen($sequence[0]['relname']);
    $firstPos = strrpos($sequence[0]['relname'], '_');
    $secondPos = strrpos($sequence[0]['relname'], '_', $firstPos - $strlen - 1);
    $table = substr($sequence[0]['relname'], 0, $secondPos);
    $field = substr($sequence[0]['relname'], $secondPos + 1, $firstPos - $secondPos - 1);
    $maxValue = $dbn->fetchAll("SELECT {$field} FROM {$table} ORDER BY {$field} DESC LIMIT 1");
    if (!empty($maxValue[0][0][$field])) {
        $newValue = $maxValue[0][0][$field] + 1;
        $dbn->execute("ALTER SEQUENCE {$sequence[0]['relname']} RESTART WITH {$newValue};");
    }
}

主要提供演算過程,對應的數值跟方法就請自己調整 ;)

原始討論: http://twpug.net/x/modules/newbb/viewtopic.php?topic_id=6297
Sign In or Register to comment.