pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedbumping all sequence ids in a schema
3+ messages / 2 participants
[nested] [flat]
* bumping all sequence ids in a schema
@ 2012-11-07 20:42 Mike Broers <mbroers@gmail.com>
2012-11-08 02:39 ` Re: bumping all sequence ids in a schema Craig Ringer <ringerc@ringerc.id.au>
0 siblings, 1 reply; 3+ messages in thread
From: Mike Broers @ 2012-11-07 20:42 UTC (permalink / raw)
To: pgsql-admin
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.
Thanks,
Mike
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: bumping all sequence ids in a schema
2012-11-07 20:42 bumping all sequence ids in a schema Mike Broers <mbroers@gmail.com>
@ 2012-11-08 02:39 ` Craig Ringer <ringerc@ringerc.id.au>
2012-11-08 16:43 ` Re: bumping all sequence ids in a schema Mike Broers <mbroers@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Craig Ringer @ 2012-11-08 02:39 UTC (permalink / raw)
To: Mike Broers <mbroers@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: bumping all sequence ids in a schema
2012-11-07 20:42 bumping all sequence ids in a schema Mike Broers <mbroers@gmail.com>
2012-11-08 02:39 ` Re: bumping all sequence ids in a schema Craig Ringer <ringerc@ringerc.id.au>
@ 2012-11-08 16:43 ` Mike Broers <mbroers@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Mike Broers @ 2012-11-08 16:43 UTC (permalink / raw)
To: Craig Ringer <ringerc@ringerc.id.au>; +Cc: pgsql-admin
Wow thanks for the code!! I'll test it out and let you know if I get any
unexpected results.
On Wed, Nov 7, 2012 at 8:39 PM, Craig Ringer <ringerc@ringerc.id.au> wrote:
> 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
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-11-08 16:43 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-11-07 20:42 bumping all sequence ids in a schema Mike Broers <mbroers@gmail.com>
2012-11-08 02:39 ` Craig Ringer <ringerc@ringerc.id.au>
2012-11-08 16:43 ` Mike Broers <mbroers@gmail.com>
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