[SQL] reinitialize a sequence?

[Dan Lyke]

Bruno Boettcher writes:
> is there a simple way to tell all sequences to take the max value +1 of
> their respective tables? (a bit like the vacuum command?)

This is completely gross, but what I've done:


#!/usr/bin/perl -w
use strict;
use DBI;
my ($dbh);
sub BEGIN
{
    $dbh = DBI->connect('DBI:Pg:dbname=xxxx',
                        'zzzzzzz',
                        'zzzzz')
        or die $DBI::errstr;
}
sub END
{
    $dbh->disconnect;
}


sub UpdateSequenceFor($)
{
    my ($table) = @_;
    my ($sql,$sth,$id,$row);

    $sql = "SELECT max(id) FROM $table";
    $sth = $dbh->prepare($sql) or die $dbh->errstr."\n$sql\n";
    $sth->execute or die $sth->errstr."\n$sql\n";
    if ($id = $sth->fetchrow_arrayref)
    {
        $id = $id->[0];
        $sql = "SELECT nextval('".$table."_id_seq')";
        $sth = $dbh->prepare($sql) or die $dbh->errstr."\n$sql\n";
        $sth->execute or die $sth->errstr."\n$sql\n";
        while (($row = $sth->fetchrow_arrayref) && ($row->[0] < $id))
        {
            $sth = $dbh->prepare($sql) or die $dbh->errstr."\n$sql\n";
            $sth->execute or die $sth->errstr."\n$sql\n";
        }
    }
}

# update the sequence for each table:
foreach ('users','blogentries','blogcomments','blogcommenthistory')
{
    UpdateSequenceFor($_);
}