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