Re: Code to automatically refresh sequences after loading data?

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