Re: bumping all sequence ids in a schema

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(at)ringerc(dot)id(dot)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
>
>