Skip site navigation (1) Skip section navigation (2)

Code to automatically refresh sequences after loading data?

From: "steve boyle" <boylesa(at)dial(dot)pipex(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Code to automatically refresh sequences after loading data?
Date: 2001-10-27 17:36:16
Message-ID: 9rerbg$25uq$1@news.tht.net (view raw or flat)
Thread:
Lists: 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(at)dial(dot)pipex(dot)com



Responses

pgsql-admin by date

Next:From: Eric DayDate: 2001-10-27 18:12:20
Subject: How can I..
Previous:From: Emmanuel GuyotDate: 2001-10-27 11:08:42
Subject: Re: pg_dump and timestamp : problem with TimeZone

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group