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
