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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
