pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Function to reset sequence..... 2+ messages / 2 participants [nested] [flat]
* Function to reset sequence..... @ 2006-10-27 16:28 Doug Hyde <doug.hyde@e-cocreate.com> 2006-11-03 23:52 ` Re: Function to reset sequence..... Anonymous <anonymous.nospam@hotmail.com> 0 siblings, 1 reply; 2+ messages in thread From: Doug Hyde @ 2006-10-27 16:28 UTC (permalink / raw) To: pgsql-sql I am a real newbie with PL/pgSQL, and with postgres, but here goes. The problem: when you import data into postgresql, there is no way to set the proper sequence on the primary key. For example, I could have a table with 2000 rows, some of which have been added/deleted in time, so the nextval for the sequence should be 3301. When you set the table up with sql as: CREATE TABLE "public"."tblcompany"( "intcompany" SERIAL PRIMARY KEY, "txtcompanyname_1" varchar(255) , ... ); The nextval is set by default to 1. To overcome this, and avoid manually resetting the key (which is error prone), my strategy is (1) create the structure, (2) import the data, and then (3) reset the primary key. I have written sql to accomplish the first two which I have working well; the third is more complicated. What I would like to try is pasted below, after messing around - I haven't really got it even close to working: CREATE OR REPLACE FUNCTION fixsequence() returns void AS $$ BEGIN x RECORD; temp int; -- set up a loop for the tables in the database FOR x IN SELECT table_name, column_name FROM information_schema.key_column_usage NATURAL JOIN information_schema.table_constraints WHERE constraint_type = 'PRIMARY KEY' AND ordinal_position = 1 ORDER BY 1; -- begin loop LOOP -- get the max value of the primary key and add 1 select max(x.column_name)+1 as temp from x.tablename; -- get the seqence name for the table, sequence name always includes the table name of the pimary key select relname as seq_name from pg_class where relkind = 'S' and relname like x.table_name'%'; -- now reset the sequence for that table SELECT setval(seq_name, temp); END LOOP; END; $LANGUAGE 'plpgsql'; Before I mess up my data, will this (or something like it work) as I have little confidence? I am having trouble with combining variables with wildcards (middle of the loop). Thanks for any support. Doug ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Function to reset sequence..... 2006-10-27 16:28 Function to reset sequence..... Doug Hyde <doug.hyde@e-cocreate.com> @ 2006-11-03 23:52 ` Anonymous <anonymous.nospam@hotmail.com> 0 siblings, 0 replies; 2+ messages in thread From: Anonymous @ 2006-11-03 23:52 UTC (permalink / raw) To: pgsql-sql It is not the answer to your question, but please note that the NATURAL JOIN may cause problems if the underlaying database structure changes (for example after a version upgrade). See the following thread for more information: http://forums.oracle.com/forums/thread.jspa?threadID=440287 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-11-03 23:52 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2006-10-27 16:28 Function to reset sequence..... Doug Hyde <doug.hyde@e-cocreate.com> 2006-11-03 23:52 ` Anonymous <anonymous.nospam@hotmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox