Thursday, 20 August 2015

PostgreSQL - Fixing Sequences

Tested on version 8.4

-- DROP FUNCTION public.SeqFix();
CREATE OR REPLACE  Function public.SeqFix() Returns void AS $$
DECLARE LIST record; MaxIDValue INTEGER; CurrentValue iNTEGER;
BEGIN
    FOR LIST iN Select table_schema, table_name, column_name, split_part(column_default,'''' ,2) AS seqname FROM information_schema.columns
                        Where table_catalog=current_database() AND column_default iS NOT NULL AND Position('nextval' iN column_default) =1 order by 1,2,3
    LOOP
        EXECUTE 'SELECT MAX(' || LIST.column_name || ') FROM ' || LIST.table_schema || '.' || LIST.table_name
        iNTO MaxIDValue;
        EXECUTE 'SELECT COUNT(*) FROM information_schema.sequences WHERE sequence_catalog=current_database() AND sequence_schema='''||LIST.table_schema||'''
             AND sequence_name='''||split_part(LIST.seqname, '.',2)||''''
        INTO CurrentValue;
        IF CurrentValue = 0 THEN
            RAISE WARNING E'?? SEQ ::\t%\t :: does not exists  ??', LIST.seqname ;
        ELSE
            EXECUTE 'SELECT last_value FROM ' || LIST.seqname
            INTO CurrentValue;
            IF CurrentValue < MaxIDValue THEN
                RAISE WARNING E'!! SEQ :: \t% = %\t<\tMAX(%.%.% = %) ', LIST.seqname, CurrentValue, LIST.table_schema, LIST.table_name, LIST.column_name, MaxIDValue;
               -- PERFORM pg_catalog.setval(LIST.seqname, MaxIDValue+1, false);
            END IF;
        END IF;
    END loop;
END;
$$ LANGUAGE plpgsql;

SELECT public.SeqFix();

No comments:

Post a Comment