Re: Script to reset all sequence values in the a given DB? - Mailing list pgsql-general

 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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Pavel Stehule
Sent: Monday, December 10, 2007 4:33 PM
To: Nathan Wilhelmi
Cc: PGSQL Mailing List
Subject: Re: [GENERAL] Script to reset all sequence values in the a
given DB?

On 10/12/2007, Nathan Wilhelmi <wilhelmi@ucar.edu> 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.