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.
If anyone can write this script in using plpythonu, I would love to
see how it is done.
create or replace function UpdateSequences() returns varchar(50) as
$$
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 ;
End LOOP;
return 1;
end
$$
language plpgsql
Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax
