Обсуждение: bumping all sequence ids in a schema
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
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.
DOChange "+ 0" to whatever your increment is.
$$
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;
--
Craig Ringer
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:
I'd use a PL/PgSQL `DO` block, myself.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.DOChange "+ 0" to whatever your increment is.
$$
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;
--
Craig Ringer