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