Re: Code to automatically refresh sequences after loading data? - Mailing list pgsql-admin
From | Ross J. Reedstrom |
---|---|
Subject | Re: Code to automatically refresh sequences after loading data? |
Date | |
Msg-id | 20011029131815.B23138@rice.edu Whole thread Raw |
In response to | Code to automatically refresh sequences after loading data? ("steve boyle" <boylesa@dial.pipex.com>) |
Responses |
Re: Code to automatically refresh sequences after loading data?
|
List | pgsql-admin |
On Sat, Oct 27, 2001 at 06:36:16PM +0100, steve boyle wrote: > Has anyone got any code that will automatically carry out setvals for serial > fields after loading data. I'm trying to write a function that will > identify and go through all sequences within the current database and 'fix' > the sequences to be set as the correct next value. > > I cannot find any way of consistently identifiying the table/field that the > sequence has been defined over by interrogating the system catalogues. You need to go grovelling through pg_attrdef, looking for the sequence name in the adsrc field. Then the adrelid field gives you the oid from pg_class of the table, and the adnum gives you the ordinal for the column that has this default, which is in pg_attribute.attnum. Be careful of other tricky uses of sequences: recent discussion on one of these lists has been about isung one sequence to generate unique ids across _multiple_ tables. I usually keep a hand edited file around with my (also hand-edited) defining schema, in which I have a bunch of: SELECT setval('sequence_name_here',max(column_name)) from tablename; Generated by a little awk/sed/grep of the schema, or from some SQL on the db. Ah, this should be useful: 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.adrelidand d.adnum = a.attnum; Here's what it does on a simple db of mine (excuse the wrap) all those quotes are to make it MixEdCase proof. ?column? --------------------------------------------------------------------------------------- SELECT setval('"people_peid_seq"', max("peid") FROM "people"; SELECT setval('"other_programs_prog_id_seq"', max("prog_id") FROM "other_programs"; SELECT setval('"other_courses_course_id_seq"', max("course_id") FROM "other_courses"; Ross > > The function so far is: > > create function sys_refresh_sequences () returns integer as ' > DECLARE > myfield RECORD; > tblname text; > BEGIN > FOR myseq IN SELECT * FROM pg_class WHERE relkind=''S'' ORDER BY > relname LOOP > myfield := substring(myseq.relname, 1, > char_length(myseq.relname)-4); > .... > .... > RAISE NOTICE ''REFRESHING SEQUENCE % ON %'', myseq.relname, > myfield; > END LOOP; > > return 1; > END; > ' language 'plpgsql'; > > Any pointers would be appreciated > > Many thanks > > Steve Boyle > boylesa@dial.pipex.com > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- 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
pgsql-admin by date: