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	fmichon	1801	--------------------------------------------------------------------------------
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();