Did you want to set to a specific known value or the min value of the
sequence. I think Pavel's sets to the min value of the sequence.
The below sets all the sequences to the same value
CREATE AGGREGATE sum ( BASETYPE = text,
SFUNC = textcat,
STYPE = text,
INITCOND = '' );
CREATE OR REPLACE FUNCTION cp_resetsequences(resetto integer)
RETURNS void AS
$BODY$
BEGIN
EXECUTE (SELECT SUM('ALTER SEQUENCE ' || sequence_schema || '.'
|| sequence_name || ' RESTART WITH ' || CAST(resetto As varchar(50)) ||
'; ' )
FROM information_schema.sequences);
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
--Note this will set all the sequences in the database to 150
SELECT cp_resetsequences(150);
-----Original Message-----
From: pgsql-general-owner@xxxxxxxxxxxxxx
[mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Pavel Stehule
Sent: Monday, December 10, 2007 4:33 PM
To: Nathan Wilhelmi
Cc: PGSQL Mailing List
Subject: Re: Script to reset all sequence values in the a
given DB?
On 10/12/2007, Nathan Wilhelmi <wilhelmi@xxxxxxxx> wrote:
> Hello - Does anyone happen to have a SQL script or function that can
> reset all the sequence values found in a given DB? When we rebuild the
> DB it would be handy to be able to set all the sequence back to a
known
> starting place.
>
create or replace function resetall()
returns void as $$
declare
v varchar;
m integer;
begin
for v in
select n.nspname || '.' || c.relname
from pg_catalog.pg_class c
left join
pg_catalog.pg_namespace n
on n.oid = c.relnamespace
where c.relkind = 'S'
loop
execute 'select min_value from '||v into m;
setval(v, m, false);
end loop;
return;
end; $$ language plpgsql;
Regards
Pavel Stehule
> Thanks!
>
> -Nate
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
