シーケンスをシャッフルする君1号

seq_shuffle.pl

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
#use DBIx::QueryLog;

my @dsn  = (...);
my $dbh = DBI->connect(@dsn, { RaiseError => 1, PrintError => 0, pg_enable_utf8 => 1 });
my $sql = "SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema = 'public'";

my $sth = $dbh->prepare($sql);
$sth->execute();

while ( my $row = $sth->fetchrow_arrayref() ) {
    my $sequence_name = $row->[0];
    shuffle_sequence($dbh, $sequence_name);
}


sub shuffle_sequence {
    my ($dbh, $sequence_name) = @_;
    my $sth = $dbh->prepare("SELECT nextval(?)");
    $sth->execute($sequence_name);
    my $row = $sth->fetchrow_arrayref()->[0];
    my $offset = int(rand(1000));
    my $new_value = $row + $offset;
    $sth = $dbh->prepare("SELECT setval(?, ?)");
    $sth->execute($sequence_name, $new_value);
}