plpgsql para corrigir o “nextId” em bases PostgreSQL (conserta as sequences)

CREATE OR REPLACE FUNCTION "public"."fixsequence"()
 RETURNS "pg_catalog"."int2" AS $BODY$declare v_reg record;
v_sql varchar;
i_count int;
 i_count := 0;

       for v_reg in (
               select t.table_name, c.column_name, t.table_schema, s.sequence_name
               from information_schema.tables t
               inner join information_schema.columns c on
                  c.table_catalog = t.table_catalog and
                  c.table_schema = t.table_schema and
                  c.table_name = t.table_name and
                  c.column_name = 'id'
               inner join information_schema.sequences s on
                  t.table_catalog = s.sequence_catalog and
                  t.table_schema = s.sequence_schema and
                  lower(s.sequence_name) = lower(t.table_name || '_id_seq')
       ) loop
               v_sql := 'SELECT setval(''"'|| v_reg.table_schema || '"."' || v_reg.sequence_name || '"'', (SELECT max("'|| v_reg.column_name ||'")+1 FROM "'||v_reg.table_schema||'"."' || v_reg.table_name || '"))';
               execute v_sql;
               i_count := i_count +1;
       end loop;
       RETURN i_count;

Obs.: Note que ali onde tem “c.column_name = ‘id'”, “id” se refere a chave primária de suas tabelas 😉

Retirado de: