Re: update sequence conversion script

Sim Zacks wrote:
> I am in the process of converting an existing database to PostGreSQL
> and wrote a generic script to update all of the sequences as they default at 1.
> I thought it would be useful to other people who are converting their
> databases.

Very nice.

> create or replace function UpdateSequences() returns varchar(50) as
> $$

For those that are puzzled, 8.0 allows you to use "dollar quoting" so
you can avoid \'\'\' in your plpgsql functions.

> declare
> seqrecord record;
> tblname varchar(50);
> fieldname varchar(50);
> maxrecord record;
> maxvalue integer;
> begin
> for seqrecord in select relname from pg_statio_user_sequences Loop
> tblname:=split_part(seqrecord.relname,'_',1);
> fieldname:=split_part(seqrecord.relname,'_',2);
> for maxrecord in execute 'select max(' || fieldname || ') as f1 from ' || tblname LOOP
> maxvalue:=maxrecord.f1;
> end loop;
> execute 'alter sequence ' || seqrecord.relname || ' restart with ' || coalesce(maxvalue,0)+1 ;

One thing you might want to test is what happens when you manually
create a sequence separate from a table, i.e. no such table-name exists.

Also, you can have more than one table relying on a single sequence (and
I have in one of my systems). Not sure there's anything useful you can
do in such a case, or how you'd detect such a situation.

--
Richard Huxton
Archonet Ltd