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@rice.edu> wrote in message
news:20011029171627.B24888@rice.edu...
> 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@rice.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