Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ringerc@ringerc.id.au>) id 1TWI1m-0004GR-37
	for pgsql-admin@postgresql.org; Thu, 08 Nov 2012 02:39:34 +0000
Received: from mail-da0-f46.google.com ([209.85.210.46])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ringerc@ringerc.id.au>) id 1TWI1k-0002qr-BE
	for pgsql-admin@postgresql.org; Thu, 08 Nov 2012 02:39:33 +0000
Received: by mail-da0-f46.google.com with SMTP id n41so965838dak.19
	for <pgsql-admin@postgresql.org>; Wed, 07 Nov 2012 18:39:30 -0800 (PST)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
	d=google.com; s=20120113;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:x-enigmail-version:content-type
	:x-gm-message-state;
	bh=CP6f4CSahqN7A9lbDJ+eCLPhZOmq7JQ6KtnPs3PB8oA=;
	b=a+tPa0bjquPx++q1y/QFGFG6nR8j3K2CjBJiyGK1zc3EdZFoPDWS2kz0AyVd/9JC3N
	aLpmOwbhNCLF7IlnqhuhBsokkhZYSHvztrQ9vao6dpmH6+a6nXhJ1B+5F53fghdHzyLo
	lAJnhWSIn7wxiPgBVeS8TKLlUAk9i3qMFTa3ziuBJkdYqzIp9+Jjk/g3xD0Tk33f0Zqq
	yWpbn3k+NTqFNCc8rbcWb5wTaSJHbIuDWhggGRtyFMHfcXee5ZuMPbJfYIVTD0wUtKx3
	7iGAsD48odSqKZPdNq+jAIQ2U3oqR7BpMFDm7iEaKl20J5lI4+7CXNkIugG+PqGlbuD9
	5+gg==
Received: by 10.68.235.71 with SMTP id uk7mr12469896pbc.10.1352342370284;
	Wed, 07 Nov 2012 18:39:30 -0800 (PST)
Received: from ayaki.localdomain (124-148-90-153.dyn.iinet.net.au.
	[124.148.90.153]) by mx.google.com with ESMTPS id
	ms11sm15072821pbc.74.2012.11.07.18.39.27
	(version=SSLv3 cipher=OTHER); Wed, 07 Nov 2012 18:39:29 -0800 (PST)
Message-ID: <509B1B5D.5010503@ringerc.id.au>
Date: Thu, 08 Nov 2012 10:39:25 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:16.0) Gecko/20121016 Thunderbird/16.0.1
MIME-Version: 1.0
To: Mike Broers <mbroers@gmail.com>
CC: pgsql-admin@postgresql.org
Subject: Re: bumping all sequence ids in a schema
References: 
 <CAB9893j=mF+GBs47RMZaNAU=YvZNzQmV30Q70mL19ac0S6Vo-g@mail.gmail.com>
In-Reply-To: 
 <CAB9893j=mF+GBs47RMZaNAU=YvZNzQmV30Q70mL19ac0S6Vo-g@mail.gmail.com>
X-Enigmail-Version: 1.4.5
Content-Type: multipart/alternative;
	boundary="------------050108020207020907080708"
X-Gm-Message-State: 
 ALoCoQkm5560i18u9ylnMvtr4cm9tLWQXmntunJVn8nnjf9tW3krk4nTH5vhJa7xbCqwms+nknqR
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201211/47
X-Sequence-Number: 40292

This is a multi-part message in MIME format.
--------------050108020207020907080708
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

On 11/08/2012 04:42 AM, Mike Broers wrote:
> I would like to bump all sequences in a schema by a specified
> increment.  Is there a stored proc or some method that is recommended?
> Currently I have sql that generates scripts to do this, but it seems
> to be an inelegant approach and before I rework it from the ground up
> I want to see if anyone has already done this kind of work or thinks
> its a wasteful pursuit for some other reason I'm overlooking.

I'd use a PL/PgSQL `DO` block, myself.

    DO
    $$
    DECLARE
        seqname text;
        nspname text;
        seqval bigint;
    BEGIN
        FOR nspname, seqname IN select n.nspname, c.relname FROM
    pg_class c INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
    WHERE c.relkind = 'S' AND n.nspname = 'public' LOOP
            EXECUTE format('SELECT last_value FROM %I.%I', nspname,
    seqname) INTO seqval;
            PERFORM setval(
    quote_ident(nspname)||'.'||quote_ident(seqname), seqval + 0);
        END LOOP;
    END;
    $$ LANGUAGE plpgsql;

Change "+ 0" to whatever your increment is.

--
Craig Ringer


--------------050108020207020907080708
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <div class="moz-cite-prefix">On 11/08/2012 04:42 AM, Mike Broers
      wrote:<br>
    </div>
    <blockquote
cite="mid:CAB9893j=mF+GBs47RMZaNAU=YvZNzQmV30Q70mL19ac0S6Vo-g@mail.gmail.com"
      type="cite">I would like to bump all sequences in a schema by a
      specified increment. &nbsp;Is there a stored proc or some method that
      is recommended? Currently I have sql that generates scripts to do
      this, but it seems to be an inelegant approach and before I rework
      it from the ground up I want to see if anyone has already done
      this kind of work or thinks its a wasteful pursuit for some other
      reason I'm overlooking.<br>
    </blockquote>
    <br>
    I'd use a PL/PgSQL `DO` block, myself.<br>
    <br>
    <blockquote>DO<br>
      $$<br>
      DECLARE<br>
      &nbsp;&nbsp;&nbsp; seqname text;<br>
      &nbsp;&nbsp;&nbsp; nspname text;<br>
      &nbsp;&nbsp;&nbsp; seqval bigint;<br>
      BEGIN<br>
      &nbsp;&nbsp;&nbsp; FOR nspname, seqname IN select n.nspname, c.relname FROM
      pg_class c INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
      WHERE c.relkind = 'S' AND n.nspname = 'public' LOOP<br>
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EXECUTE format('SELECT last_value FROM %I.%I', nspname,
      seqname) INTO seqval;<br>
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PERFORM setval(
      quote_ident(nspname)||'.'||quote_ident(seqname), seqval + 0);<br>
      &nbsp;&nbsp;&nbsp; END LOOP;<br>
      END;<br>
      $$ LANGUAGE plpgsql;<br>
      <br>
    </blockquote>
    Change "+ 0" to whatever your increment is.<br>
    <br>
    --<br>
    Craig Ringer<br>
    <br>
  </body>
</html>

--------------050108020207020907080708--