data/pgsql/update_sequences.sql

--------------------------------------------------------------------------------
-- Mise à jour des séquences avec le max + 1
--
-- @package openfoncier
-- @version SVN : $Id$
--------------------------------------------------------------------------------

--
CREATE OR REPLACE FUNCTION fn_fixsequences() RETURNS integer AS
$BODY$
DECLARE
themax BIGINT;
mytables RECORD;
num integer;
BEGIN
 num := 0;
 FOR mytables IN
    SELECT  S.relname as seq, C.attname as attname, T.relname as relname
    FROM pg_class AS S, pg_depend AS D, pg_class AS T, pg_attribute AS C
    WHERE S.relkind = 'S'
        AND S.oid = D.objid
        AND D.refobjid = T.oid
        AND D.refobjid = C.attrelid
        AND D.refobjsubid = C.attnum
 LOOP
      EXECUTE 'SELECT MAX('||mytables.attname||') FROM '||mytables.relname||';' INTO themax;
      IF (themax is null OR themax < 0) THEN
       themax := 0;
      END IF;
      themax := themax +1;
      EXECUTE 'ALTER SEQUENCE ' || mytables.seq || ' RESTART WITH '||themax;
      num := num + 1;
  END LOOP;
  
  RETURN num;
  
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

--
select fn_fixsequences();
1	--------------------------------------------------------------------------------
2	-- Mise à jour des séquences avec le max + 1
3	--
4	-- @package openfoncier
5	-- @version SVN : $Id$
6	--------------------------------------------------------------------------------
7
8	--
9	CREATE OR REPLACE FUNCTION fn_fixsequences() RETURNS integer AS
10	$BODY$
11	DECLARE
12	themax BIGINT;
13	mytables RECORD;
14	num integer;
15	BEGIN
16	num := 0;
17	FOR mytables IN
18	SELECT S.relname as seq, C.attname as attname, T.relname as relname
19	FROM pg_class AS S, pg_depend AS D, pg_class AS T, pg_attribute AS C
20	WHERE S.relkind = 'S'
21	AND S.oid = D.objid
22	AND D.refobjid = T.oid
23	AND D.refobjid = C.attrelid
24	AND D.refobjsubid = C.attnum
25	LOOP
26	EXECUTE 'SELECT MAX('\|\|mytables.attname\|\|') FROM '\|\|mytables.relname\|\|';' INTO themax;
27	IF (themax is null OR themax < 0) THEN
28	themax := 0;
29	END IF;
30	themax := themax +1;
31	EXECUTE 'ALTER SEQUENCE ' \|\| mytables.seq \|\| ' RESTART WITH '\|\|themax;
32	num := num + 1;
33	END LOOP;
34
35	RETURN num;
36
37	END;
38	$BODY$
39	LANGUAGE 'plpgsql' VOLATILE;
40
41	--
42	select fn_fixsequences();