Re: 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
>