Re: Code to automatically refresh sequences after loading data?

Ross, I've used your code and created a function that seems to do the job.

The function is shown below.
--
-- Function: sys_refresh_sequences()
-- Purpose: Refreshes all sequences in the current database after data
load.
-- Notes: Code for sequence_setvals provided by Ross J Reedstorm
-- Wrapper function added by Steve Boyle 30/10/2001
--
drop view sequence_setvals;
create view sequence_setvals as
select 'SELECT setval(''"'||cs.relname||'"'', max("'||attname||'")) FROM
"'||c.relname||'";' AS expr from pg_class c, pg_class cs, pg_attribute a,
pg_attrdef d where cs.relkind = 'S' and d.adsrc ~ cs.relname and c.oid =
a.attrelid and c.oid = d.adrelid and d.adnum = a.attnum;

drop function sys_refresh_sequences();
create function sys_refresh_sequences () returns integer as '
DECLARE
myseq RECORD;
BEGIN
FOR myseq IN select * from sequence_setvals LOOP
RAISE NOTICE ''Executing - %'', myseq.expr;
EXECUTE myseq.expr;
END LOOP;

return 1;
END;
' language 'plpgsql';

""Ross J. Reedstrom"" <reedstrm(at)rice(dot)edu> wrote in message
news:20011029171627(dot)B24888(at)rice(dot)edu(dot)(dot)(dot)
> On Mon, Oct 29, 2001 at 01:18:15PM -0600, Ross J. Reedstrom wrote:
>
> Need to fix that: I was short a right paren:
>
> select 'SELECT setval(''"'||cs.relname||'"'', max("'||attname||'"))
> FROM "'||c.relname||'";' from pg_class c, pg_class cs, pg_attribute a,
> pg_attrdef d where cs.relkind = 'S' and d.adsrc ~ cs.relname and c.oid =
> a.attrelid and c.oid = d.adrelid and d.adnum = a.attnum;
>
> now you can do (in psql):
>
> bioinfo=# \t
> Showing only tuples.
> bioinfo=# select 'SELECT setval(''"'||cs.relname||
> '"'', max("'||attname||'")) FROM "'||c.relname||'";'
> from pg_class c, pg_class cs, pg_attribute a, pg_attrdef d
> where cs.relkind = 'S' and d.adsrc ~ cs.relname
> and c.oid = a.attrelid and c.oid = d.adrelid
> and d.adnum = a.attnum \g fix-serial.sql
>
> bioinfo=# \i fix-serial.sql
> 77
>
> 78
>
> 17
>
> bioinfo=#
>
> i.e. use SQL to generate the SQL into a file, then read the commands back
> in from that file.
>
> Note that since this grovels around in systemtables, it can break with
version
> changes.
>
> Ross
>
> --
> Ross Reedstrom, Ph.D. reedstrm(at)rice(dot)edu
> Executive Director phone: 713-348-6166
> Gulf Coast Consortium for Bioinformatics fax: 713-348-6182
> Rice University MS-39
> Houston, TX 77005
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org