From: Craig Ringer <ringerc@ringerc.id.au>
To: Mike Broers <mbroers@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: bumping all sequence ids in a schema
Date: Thu, 08 Nov 2012 10:39:25 +0800
Message-ID: <509B1B5D.5010503@ringerc.id.au> (raw)
In-Reply-To: <CAB9893j=mF+GBs47RMZaNAU=YvZNzQmV30Q70mL19ac0S6Vo-g@mail.gmail.com>
References: <CAB9893j=mF+GBs47RMZaNAU=YvZNzQmV30Q70mL19ac0S6Vo-g@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ringerc@ringerc.id.au, mbroers@gmail.com
Subject: Re: bumping all sequence ids in a schema
In-Reply-To: <509B1B5D.5010503@ringerc.id.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox