Le portail francophone de la géomatique

CREATE SEQUENCE te_relation3.traceavctube_fid_seq
  INCREMENT 1
  MINVALUE 150040
  MAXVALUE 9223372036854775807
  START 152576
  CACHE 1;
ALTER TABLE te_relation3.traceavctube_fid_seq
  OWNER TO postgres;

-- Function: public.rebuilt_sequences()

-- DROP FUNCTION public.rebuilt_sequences();

CREATE OR REPLACE FUNCTION public.rebuilt_sequences()
  RETURNS integer AS
$BODY$
  DECLARE sequencedefs RECORD; c integer ;
  BEGIN
    FOR sequencedefs IN Select
    constraint_column_usage.table_schema as schemaname,
    constraint_column_usage.table_name as tablename, 
    constraint_column_usage.column_name as columnname,
    replace(replace(columns.column_default,'''::regclass)',''),'nextval(''','') as sequencename
from 
    information_schema.constraint_column_usage, information_schema.columns
where 
    constraint_column_usage.table_schema not in ('public', 'topology') AND 
    columns.table_schema not in ('public', 'topology') AND 
    columns.table_schema=constraint_column_usage.table_schema AND
    columns.table_name=constraint_column_usage.table_name AND
    constraint_column_usage.column_name = columns.column_name AND
    columns.column_default is not null
order by    
    schemaname, tablename, columnname
   LOOP    
      EXECUTE 'select max('||sequencedefs.columnname||') from ' || sequencedefs.schemaname || '.' || sequencedefs.tablename INTO c;
      IF c is null THEN c = 0; END IF;
      IF c is not null THEN c = c+ 1; END IF;
      EXECUTE 'alter sequence ' || sequencedefs.sequencename ||' restart  with ' || c;
   END LOOP;

   RETURN 1; END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.rebuilt_sequences()
  OWNER TO postgres;
COMMENT ON FUNCTION public.rebuilt_sequences() IS 'Réinitialisation de toutes les séquences de l''instance ; mise à jour de la valeur max.';