Re: Code to automatically refresh sequences after loading data?

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.adrelid and 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(at)dial(dot)pipex(dot)com
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
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