From: Doug Hyde <doug.hyde@e-cocreate.com>
To: pgsql-sql@postgresql.org
Subject: Function to reset sequence.....
Date: Fri, 27 Oct 2006 12:28:53 -0400
Message-ID: <001601c6f9e4$fe2d5000$6501a8c0@laptop> (raw)
In-Reply-To: <200610231049.16507.fluca1978@infinito.it>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: doug.hyde@e-cocreate.com
Subject: Re: Function to reset sequence.....
In-Reply-To: <001601c6f9e4$fe2d5000$6501a8c0@laptop>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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