Code to automatically refresh sequences after loading data? - Mailing list pgsql-admin

From steve boyle
Subject Code to automatically refresh sequences after loading data?
Date
Msg-id 9rerbg$25uq$1@news.tht.net
Whole thread Raw
Responses Re: Code to automatically refresh sequences after loading data?  ("Ross J. Reedstrom" <reedstrm@rice.edu>)
List pgsql-admin
Has anyone got any code that will automatically carry out setvals for serial
fields after loading data.  I'm trying to write a function that will
identify and go through all sequences within the current database and 'fix'
the sequences to be set as the correct next value.

I cannot find any way of consistently identifiying the table/field that the
sequence has been defined over by interrogating the system catalogues.

The function so far is:

create function sys_refresh_sequences () returns integer as '
    DECLARE
        myfield RECORD;
        tblname text;
    BEGIN
        FOR myseq IN SELECT * FROM pg_class WHERE relkind=''S'' ORDER BY
relname LOOP
            myfield := substring(myseq.relname, 1,
char_length(myseq.relname)-4);
            ....
            ....
            RAISE NOTICE ''REFRESHING SEQUENCE % ON %'', myseq.relname,
myfield;
        END LOOP;

        return 1;
    END;
' language 'plpgsql';

Any pointers would be appreciated

Many thanks

Steve Boyle
boylesa@dial.pipex.com



pgsql-admin by date:

Previous
From: Tariq Muhammad
Date:
Subject: Vacuumdb warnings
Next
From: Stuart Bishop
Date:
Subject: Re: PG 7.1.3 & MacOS X (10.1)