--
-- E-MAJ : logs and rollbacks table updates : V 0.5
-- 
-- This script creates all objects needed to log and rollback table updates.
--
-- These objects are recorded into a specific schema, named "emaj". 
-- It mainly contains few technical tables, a set of functions, and one "log table" per processed application table.
--
-- This script must be executed by a role having SUPERUSER privileges.
-- Before its execution:
-- 	-> the concerned cluster must contain a tablespace named "tspemaj", for instance previously created by
--	   CREATE TABLESPACE tspemaj LOCATION '/.../tspemaj',
--	-> the plpgsql language must have been created in the concerned database.

\set ON_ERROR_STOP

BEGIN TRANSACTION;

------------------------------------
--                                --
-- emaj schema and tables         --
--                                --
------------------------------------

-- (re)creation of the schema 'emaj' containing all the needed objets

DROP SCHEMA IF EXISTS emaj CASCADE;
CREATE SCHEMA emaj;

-- uncomment the next line to let emaj schema visible to all user (for test purpose)
--GRANT USAGE ON SCHEMA emaj TO PUBLIC;

-- creation of the technical tables

-- table containing the history of operations 
CREATE TABLE emaj.emaj_hist (
    oper_datetime  TIMESTAMPTZ  NOT NULL DEFAULT clock_timestamp() PRIMARY KEY,
    oper_type      TEXT         NOT NULL, 
    oper_object    TEXT,
    oper_wording   TEXT,
    oper_user      TEXT         DEFAULT session_user
    ) TABLESPACE tspemaj;

-- table containing the active marks (the marks set at or after start_group) 
CREATE TABLE emaj.emaj_mark (
    mark_datetime  TIMESTAMPTZ  NOT NULL PRIMARY KEY,
    mark_group     TEXT,
    mark_name      TEXT
    ) TABLESPACE tspemaj;

-- table containing the tables groups
CREATE TABLE emaj.emaj_group (
    group_name     TEXT         NOT NULL,
    group_schema   TEXT         NOT NULL,
    group_table    TEXT         NOT NULL,
    PRIMARY KEY (group_name, group_schema, group_table)
    ) TABLESPACE tspemaj;

-- table containing the sequences log 
-- (to record the state at mark time of application sequences and sequences used by log tables) 
CREATE TABLE emaj.emaj_sequence (
    sequ_schema    TEXT         NOT NULL,
    sequ_name      TEXT         NOT NULL,
    sequ_datetime  TIMESTAMPTZ  NOT NULL,
    sequ_mark      TEXT         NOT NULL,
    sequ_last_val  BIGINT       NOT NULL,
    sequ_start_val BIGINT       NOT NULL,
    sequ_increment BIGINT       NOT NULL,
    sequ_max_val   BIGINT       NOT NULL,
    sequ_min_val   BIGINT       NOT NULL,
    sequ_cache_val BIGINT       NOT NULL,
    sequ_is_cycled BOOLEAN      NOT NULL,
    sequ_is_called BOOLEAN      NOT NULL,
    PRIMARY KEY (sequ_schema, sequ_name, sequ_datetime)
    ) TABLESPACE tspemaj;

-- uncomment the 4 next lines to let emaj tables visible to all user (for test purpose)
--GRANT SELECT ON emaj.emaj_hist TO PUBLIC;
--GRANT SELECT ON emaj.emaj_mark TO PUBLIC;
--GRANT SELECT ON emaj.emaj_group TO PUBLIC;
--GRANT SELECT ON emaj.emaj_sequence TO PUBLIC;

------------------------------------
--                                --
-- Low level Functions            --
--                                --
------------------------------------

CREATE or REPLACE FUNCTION emaj.emaj_check_class(v_schemaName TEXT, v_className TEXT) RETURNS TEXT AS
$emaj_check_class$
-- This function verifies that an application table or sequence exists in pg_class
-- It also protects from a recursive use : tables or sequences from emaj schema cannot be managed by EMAJ
-- Input: the names of the schema and the class (table or sequence)
-- Output: the relkind of the class : 'r' for a table and 's' for a sequence
-- If the schema or the class is not known, the function stops.
  DECLARE
    v_relkind      TEXT;
    v_schemaOid    OID;
  BEGIN
    IF v_schemaName = 'emaj' THEN
      RAISE EXCEPTION 'emaj_check_class : object from schema % cannot be managed by EMAJ', v_schemaName;
    END IF;
    SELECT oid INTO v_schemaOid FROM pg_namespace WHERE nspname = v_schemaName;
    IF NOT found THEN
      RAISE EXCEPTION 'emaj_check_class : schema % doesn''t exist', v_schemaName;
    END IF;
    SELECT relkind INTO v_relkind FROM pg_class 
      WHERE relNameSpace = v_schemaOid AND relName = v_className AND relkind in ('r','S');
    IF NOT found THEN
      RAISE EXCEPTION 'emaj_check_class : table or sequence % doesn''t exist', v_className;
    END IF; 
    RETURN v_relkind;
  END;
$emaj_check_class$ LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION emaj.emaj_create_log(v_schemaName TEXT, v_tableName TEXT) RETURNS void AS 
$emaj_create_log$
-- This function creates all what is needed to manage the log and rollback operations for an application table
-- Input: schema name (mandatory even for the 'public' schema) and table name
-- Are created: 
--    - the associated log table, with its own sequence
--    - the function that logs the tables updates, defined as a trigger
--    - the rollback function (one per table)

  DECLARE
-- variables for the name of tables, functions, triggers,...
    v_fullTableName TEXT;
    v_emajSchema    TEXT := 'emaj';
    v_emajTblSpace  TEXT := 'tspemaj';
    v_logTableName  TEXT;
    v_logFnctName   TEXT;
    v_rlbkFnctName  TEXT;
    v_triggerName   TEXT;
-- variables to hold pieces of SQL
    v_pkCondList    TEXT;
    v_colList       TEXT;
    v_setList       TEXT;
-- other variables
    v_attname       TEXT;
    v_relhaspkey    BOOLEAN;
-- cursor to retrieve all columns of the application table
    col1_curs CURSOR (tbl regclass) FOR 
      SELECT attname FROM pg_attribute 
        WHERE attrelid = tbl 
          AND attnum > 0
          AND attisdropped = false;
-- cursor to retrieve all columns of table's primary key
    col2_curs CURSOR (tbl regclass) FOR 
      SELECT attname FROM pg_attribute, pg_index 
        WHERE pg_attribute.attrelid = pg_index.indexrelid 
          AND indrelid = tbl 
          AND attnum > 0 AND attisdropped = false;
-- cursor to retrive all columns that are not in the primary key
    col3_curs CURSOR (tbl regclass) FOR 
      SELECT attname FROM pg_attribute 
        WHERE attrelid = tbl 
          AND attnum > 0 
          AND attisdropped = false
      EXCEPT
      SELECT attname FROM pg_attribute, pg_index 
        WHERE pg_attribute.attrelid = pg_index.indexrelid 
          AND indrelid = tbl 
          AND attnum > 0 AND attisdropped = false;

  BEGIN
-- check the table has a primary key
    BEGIN
      SELECT relhaspkey INTO v_relhaspkey FROM pg_class, pg_namespace WHERE 
        relnamespace = pg_namespace.oid AND nspname = v_schemaName AND relname = v_tableName;
      IF NOT FOUND THEN
        RAISE EXCEPTION 'emaj_create_log: Internal error - schema.table not found in pg_class';
      END IF;
    END;
    IF v_relhaspkey = FALSE THEN
      RAISE EXCEPTION 'emaj_create_log : table % has no PRIMARY KEY', v_tableName;
    END IF;
-- OK, build the different name for table, trigger, functions,...
    v_fullTableName := quote_ident(v_schemaName) || '.' || quote_ident(v_tableName);
    v_logTableName  := quote_ident(v_emajSchema) || '.' || quote_ident(v_schemaName || '_' || v_tableName || '_log');
    v_logFnctName   := quote_ident(v_emajSchema) || '.' || quote_ident(v_schemaName || '_' || v_tableName || '_log_fnct');
    v_rlbkFnctName  := quote_ident(v_emajSchema) || '.' || quote_ident(v_schemaName || '_' || v_tableName || '_rlbk_fnct');
    v_triggerName   := quote_ident(v_schemaName || '_' || v_tableName || '_emaj_trg');

-- creation of the log table: the log table looks like the application table, with some additional technical columns
    EXECUTE 'DROP TABLE IF EXISTS ' || v_logTableName;
    EXECUTE 'CREATE TABLE ' || v_logTableName
         || '( LIKE ' || v_fullTableName || ') TABLESPACE ' || v_emajTblSpace ;
    EXECUTE 'ALTER TABLE ' || v_logTableName
         || ' ADD COLUMN emaj_verb VARCHAR(3)';
    EXECUTE 'ALTER TABLE ' || v_logTableName
         || ' ADD COLUMN emaj_tuple VARCHAR(3)';
    EXECUTE 'ALTER TABLE ' || v_logTableName
         || ' ADD COLUMN emaj_id BIGSERIAL';
    EXECUTE 'ALTER TABLE ' || v_logTableName
         || ' ADD COLUMN emaj_changed TIMESTAMPTZ DEFAULT current_timestamp';
    EXECUTE 'ALTER TABLE ' || v_logTableName
         || ' ADD COLUMN emaj_user VARCHAR(32) DEFAULT session_user';

-- creation of the log fonction that will be mapped to the log trigger later
-- The new row is logged for each INSERT, the old row is logged for each DELETE 
-- and the old and the new rows are logged for each UPDATE
    EXECUTE 'CREATE or REPLACE FUNCTION ' || v_logFnctName || '() RETURNS trigger AS $logfnct$'
         || '  BEGIN'
         || '    IF (TG_OP = ''DELETE'') THEN'
         || '      INSERT INTO ' || v_logTableName || ' SELECT OLD.*, ''DEL'', ''OLD'';'
         || '      RETURN OLD;'
         || '    ELSIF (TG_OP = ''UPDATE'') THEN'
         || '      INSERT INTO ' || v_logTableName || ' SELECT OLD.*, ''UPD'', ''OLD'';'
         || '      INSERT INTO ' || v_logTableName || ' SELECT NEW.*, ''UPD'', ''NEW'';'
         || '      RETURN NEW;'
         || '    ELSIF (TG_OP = ''INSERT'') THEN'
         || '      INSERT INTO ' || v_logTableName || ' SELECT NEW.*, ''INS'', ''NEW'';'
         || '      RETURN NEW;'
         || '    END IF;'
         || '    RETURN NULL;'
         || '  END;'
         || '$logfnct$ LANGUAGE plpgsql SECURITY DEFINER;';

-- creation of the trigger on the application table, using the previously created log function 
-- But the trigger is not immediately activated (it will be at emaj_start_group time)
    EXECUTE 'DROP TRIGGER IF EXISTS ' || v_triggerName || ' ON ' || v_fullTableName;
    EXECUTE 'CREATE TRIGGER ' || v_triggerName
         || ' AFTER INSERT OR UPDATE OR DELETE ON ' || v_fullTableName
         || '  FOR EACH ROW EXECUTE PROCEDURE ' || v_logFnctName || '()';
    EXECUTE 'ALTER TABLE ' || v_fullTableName || ' DISABLE TRIGGER ' || v_triggerName;
--
-- creation of the rollback function
--
-- First build some pieces of the CREATE FUNCTION statement
--   build the tables's columns list
    v_colList := '';
    OPEN col1_curs (v_fullTableName);
    LOOP
      FETCH col1_curs INTO v_attname;
      EXIT WHEN NOT FOUND;
      IF v_colList = '' THEN
         v_colList := 'rec_log.' || v_attname;
      ELSE
         v_colList := v_colList || ', rec_log.' || v_attname;
      END IF;
    END LOOP;
    CLOSE col1_curs;
--   build "equality on the primary key" conditions, from the list of the primary key's columns
    v_pkCondList := '';
    OPEN col2_curs (v_fullTableName);
    LOOP
      FETCH col2_curs INTO v_attname;
      EXIT WHEN NOT FOUND;
      IF v_pkCondList = '' THEN
         v_pkCondList := v_attname || ' = rec_log.' || v_attname;
      ELSE
         v_pkCondList := v_pkCondList || ' AND ' || v_attname || ' = rec_log.' || v_attname;
      END IF;
    END LOOP;
    CLOSE col2_curs;
--   build the SET clause for the UPDATE, from the list of the columns that don't belong to the primary key
    v_setList := '';
    OPEN col3_curs (v_fullTableName);
    LOOP
      FETCH col3_curs INTO v_attname;
      EXIT WHEN NOT FOUND;
      IF v_setList = '' THEN
         v_setList := v_attname || ' = rec_old_log.' || v_attname;
      ELSE
         v_setList := v_setList || ', ' || v_attname || ' = rec_old_log.' || v_attname;
      END IF;
    END LOOP;
    CLOSE col3_curs;
-- Then finaly create the rollback function associated to the table
-- At execution, it will loop on each row from the log table in reverse order
--  It will insert the old deleted rows, delete the new inserted row 
--  and update the new rows by setting back the old rows
-- The function returns the number of rollbacked elementary operations or rows
-- All these functions will be called by the emaj_rlbk_table function, which is activated by the
--  emaj_rollback_group function
    EXECUTE 'CREATE or REPLACE FUNCTION ' || v_rlbkFnctName || ' (v_rollback_id_limit bigint)'
         || ' RETURNS bigint AS $rlbkfnct$'
         || '  DECLARE'
         || '    v_nb_rows       bigint := 0;'
         || '    v_nb_proc_rows  integer;'
         || '    rec_log     ' || v_logTableName || '%ROWTYPE;'
         || '    rec_old_log ' || v_logTableName || '%ROWTYPE;'
         || '    log_curs CURSOR FOR '
         || '      SELECT * FROM ' || v_logTableName
         || '        WHERE emaj_id >= v_rollback_id_limit '
         || '        ORDER BY emaj_id DESC;'
         || '  BEGIN'
         || '    OPEN log_curs;'
         || '    LOOP '
         || '      FETCH log_curs INTO rec_log;'
         || '      EXIT WHEN NOT FOUND;'
         || '      IF rec_log.emaj_verb = ''INS'' THEN'
--       || '          RAISE NOTICE ''emaj_id = % ; INS'', rec_log.emaj_id;'
         || '          DELETE FROM ' || v_fullTableName || ' WHERE ' || v_pkCondList || ';'
         || '      ELSIF rec_log.emaj_verb = ''UPD'' THEN'
--       || '          RAISE NOTICE ''emaj_id = % ; UPD ; %'', rec_log.emaj_id,rec_log.emaj_tuple;'
         || '          FETCH log_curs into rec_old_log;'
--       || '          RAISE NOTICE ''emaj_id = % ; UPD ; %'', rec_old_log.emaj_id,rec_old_log.emaj_tuple;'
         || '          UPDATE ' || v_fullTableName || ' SET ' || v_setList || ' WHERE ' || v_pkCondList || ';'
         || '      ELSIF rec_log.emaj_verb = ''DEL'' THEN'
--       || '          RAISE NOTICE ''emaj_id = % ; DEL'', rec_log.emaj_id;'
         || '          INSERT INTO ' || v_fullTableName || ' VALUES (' || v_colList || ');'
         || '      ELSE'
         || '          RAISE EXCEPTION ''' || v_rlbkFnctName || ': internal error - emaj_verb = % unknown, emaj_id = %'','
         || '            rec_log.emaj_verb, rec_log.emaj_id;' 
         || '      END IF;'
         || '      GET DIAGNOSTICS v_nb_proc_rows = ROW_COUNT;'
         || '      IF v_nb_proc_rows <> 1 THEN'
         || '        RAISE EXCEPTION ''' || v_rlbkFnctName || ': internal error - emaj_verb = %, emaj_id = %, # processed rows = % '''
         || '           ,rec_log.emaj_verb, rec_log.emaj_id, v_nb_proc_rows;' 
         || '      END IF;'
         || '      v_nb_rows := v_nb_rows + 1;'
         || '    END LOOP;'
         || '    CLOSE log_curs;'
--       || '    RAISE NOTICE ''Table ' || v_fullTableName || ' -> % rollbacked rows'', v_nb_rows;'
         || '    RETURN v_nb_rows;'
         || '  END;'
         || '$rlbkfnct$ LANGUAGE plpgsql;';

    RETURN;
  END;
$emaj_create_log$ LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION emaj.emaj_delete_log(v_schemaName TEXT, v_tableName TEXT) RETURNS void AS 
$emaj_delete_log$
-- The function deletes all what has been created by emaj_create_log
-- Required inputs: schema name (mandatory even if "public") and table name
  DECLARE
    v_emajSchema     TEXT := 'emaj';
    v_fullTableName  TEXT;
    v_logTableName   TEXT;
    v_logFnctName    TEXT;
    v_rlbkFnctName   TEXT;
    v_triggerName    TEXT;

  BEGIN
    v_fullTableName  := quote_ident(v_schemaName) || '.' || quote_ident(v_tableName);
    v_logTableName   := quote_ident(v_emajSchema) || '.' || quote_ident(v_schemaName || '_' || v_tableName || '_log');
    v_logFnctName    := quote_ident(v_emajSchema) || '.' || quote_ident(v_schemaName || '_' || v_tableName || '_log_fnct');
    v_rlbkFnctName   := quote_ident(v_emajSchema) || '.' || quote_ident(v_schemaName || '_' || v_tableName || '_rlbk_fnct');
    v_triggerName    := quote_ident(v_schemaName || '_' || v_tableName || '_emaj_trg');

-- delete the trigger on the application table 
    EXECUTE 'DROP TRIGGER IF EXISTS ' || v_triggerName || ' ON ' || v_fullTableName;
-- delete both log and rollback functions
    EXECUTE 'DROP FUNCTION IF EXISTS ' || v_logFnctName || '()';
    EXECUTE 'DROP FUNCTION IF EXISTS ' || v_rlbkFnctName || '(bigint)';
-- delete the log table
    EXECUTE 'DROP TABLE IF EXISTS ' || v_logTableName || ' CASCADE';

    RETURN;
  END;
$emaj_delete_log$ LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION emaj.emaj_delete_seq(v_schemaName TEXT, v_seqName TEXT) RETURNS void AS 
$emaj_delete_seq$
-- The function deletes the rows stored into emaj_sequence for a particular sequence
-- Required inputs: schema name and sequence name
  BEGIN
-- delete rows from emaj_sequence 
    EXECUTE 'DELETE FROM emaj.emaj_sequence WHERE sequ_schema = ''' || v_schemaName || ''' AND sequ_name = ''' || v_seqName || '''';
    RETURN;
  END;
$emaj_delete_seq$ LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION emaj.emaj_rlbk_table(v_schemaName TEXT, v_tableName TEXT, v_timestamp TIMESTAMPTZ) RETURNS void AS 
$emaj_rlbk_table$
-- This function rollbacks one table to a given timestamp
-- The function is used by emaj.emaj_rollback_group
-- Input: schema name and table name, timestamp limit for rollback
  DECLARE
    v_emajSchema    TEXT := 'emaj';
    v_fullTableName TEXT;
    v_logTableName  TEXT;
    v_rlbkFnctName  TEXT;
    v_triggerName   TEXT;
    v_fullSeqName   TEXT;
    v_emaj_id       BIGINT;
    v_nb_rows       BIGINT;
  BEGIN
    v_fullTableName := quote_ident(v_schemaName) || '.' || quote_ident(v_tableName);
    v_logTableName     := quote_ident(v_emajSchema) || '.' || quote_ident(v_schemaName || '_' || v_tableName || '_log');
    v_rlbkFnctName := quote_ident(v_emajSchema) || '.' || 
                     quote_ident(v_schemaName || '_' || v_tableName || '_rlbk_fnct');
    v_triggerName    := quote_ident(v_schemaName || '_' || v_tableName || '_emaj_trg');
    v_fullSeqName := quote_ident(v_schemaName || '_' || v_tableName || '_log_emaj_id_seq');
-- get the emaj_id to rollback to from the sequence (first emaj_id to delete)
    SELECT CASE WHEN sequ_is_called THEN sequ_last_val + sequ_increment ELSE sequ_last_val END INTO v_emaj_id
      FROM emaj.emaj_sequence
      WHERE sequ_schema = 'emaj' AND sequ_name = v_fullSeqName AND sequ_datetime = v_timestamp;
    IF NOT FOUND THEN
       RAISE EXCEPTION 'emaj_rlbk_table: internal error - sequence for % and % not found in emaj_sequence',v_fullSeqName, v_timestamp;
    END IF;
-- insert begin event in history
    INSERT INTO emaj.emaj_hist (oper_type, oper_object, oper_wording) 
      VALUES ('ROLLBACK_TABLE BEGIN', v_fullTableName, 'Up to log_id ' || v_emaj_id);
-- first deactivate the trigger on the application table
    EXECUTE 'ALTER TABLE ' || v_fullTableName || ' DISABLE TRIGGER ' || v_triggerName;
-- rollback the table
    EXECUTE 'SELECT ' || v_rlbkFnctName || '(' || v_emaj_id || ')' INTO v_nb_rows;
-- suppress the rollbacked log part
    EXECUTE 'DELETE FROM ' || v_logTableName || ' WHERE emaj_id >= ' || v_emaj_id;
-- re-activate the trigger on the application table
    EXECUTE 'ALTER TABLE ' || v_fullTableName || ' ENABLE TRIGGER ' || v_triggerName;
-- suppress from emaj_sequence table the row regarding the emaj sequence for this application table
    DELETE FROM emaj.emaj_sequence 
      WHERE sequ_schema = 'emaj' AND sequ_name = v_fullSeqName AND sequ_datetime = v_timestamp;
-- insert end event in history
    INSERT INTO emaj.emaj_hist (oper_type, oper_object, oper_wording) 
      VALUES ('ROLLBACK_TABLE END', v_fullTableName, ' -> ' || v_nb_rows || ' rollbacked rows ');
    RETURN;
  END;
$emaj_rlbk_table$ LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION emaj.emaj_rlbk_sequence(v_schemaName TEXT, v_seqName TEXT, v_timestamp TIMESTAMPTZ) RETURNS void AS 
$emaj_rlbk_sequence$
-- This function rollbacks one sequence to a given mark
-- The function is used by emaj.emaj_rollback_group
-- Input: schema name and table name, mark
  DECLARE
    v_pgversion     TEXT := substring (version() from E'PostgreSQL\\s(\\d+\\.\\d+)\\.');
    v_fullSeqName   TEXT;
    v_stmt          TEXT;
    mark_seq_rec    RECORD;
    curr_seq_rec    RECORD;

  BEGIN
-- Read sequence's characteristics at mark time
    BEGIN
      SELECT   sequ_schema, sequ_name, sequ_mark, sequ_last_val, sequ_start_val, sequ_increment,
               sequ_max_val, sequ_min_val, sequ_cache_val, sequ_is_cycled, sequ_is_called
        INTO STRICT mark_seq_rec 
        FROM emaj.emaj_sequence 
        WHERE sequ_schema = v_schemaName AND sequ_name = v_seqName AND sequ_datetime = v_timestamp;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          RAISE EXCEPTION 'emaj_rlbk_sequence: Mark at % not found for sequence % / %', v_timestamp, v_schemaName, v_seqName;
        WHEN TOO_MANY_ROWS THEN
          RAISE EXCEPTION 'emaj_rlbk_sequence: Internal error 1';
    END;
-- Read the current sequence's characteristics
    v_fullSeqName := quote_ident(v_schemaName) || '.' || quote_ident(v_seqName);
    v_stmt = 'SELECT last_value, ';
    IF v_pgversion <= '8.3' THEN
       v_stmt = v_stmt || '0 as start_value, '; 
    ELSE
       v_stmt = v_stmt || 'start_value, ';
    END IF;
    v_stmt = v_stmt || 'increment_by, max_value, min_value, cache_value, is_cycled, is_called FROM '
                    || v_fullSeqName;
    EXECUTE v_stmt INTO STRICT curr_seq_rec;
-- Build the ALTER SEQUENCE statement, depending on the differences between the present values and the related 
--   values at the requested mark time
    v_stmt='';
    IF curr_seq_rec.last_value <> mark_seq_rec.sequ_last_val OR 
       curr_seq_rec.is_called <> mark_seq_rec.sequ_is_called THEN
      IF mark_seq_rec.sequ_is_called THEN
        v_stmt=v_stmt || ' RESTART ' || mark_seq_rec.sequ_last_val + mark_seq_rec.sequ_increment;
      ELSE
        v_stmt=v_stmt || ' RESTART ' || mark_seq_rec.sequ_last_val;
      END IF;
    END IF;
    IF curr_seq_rec.start_value <> mark_seq_rec.sequ_start_val THEN
      v_stmt=v_stmt || ' START ' || mark_seq_rec.sequ_start_val;
    END IF;
    IF curr_seq_rec.increment_by <> mark_seq_rec.sequ_increment THEN
      v_stmt=v_stmt || ' INCREMENT ' || mark_seq_rec.sequ_increment;
    END IF;
    IF curr_seq_rec.min_value <> mark_seq_rec.sequ_min_val THEN
      v_stmt=v_stmt || ' MINVALUE ' || mark_seq_rec.sequ_min_val;
    END IF;
    IF curr_seq_rec.max_value <> mark_seq_rec.sequ_max_val THEN
      v_stmt=v_stmt || ' MAXVALUE ' || mark_seq_rec.sequ_max_val;
    END IF;
    IF curr_seq_rec.cache_value <> mark_seq_rec.sequ_cache_val THEN
      v_stmt=v_stmt || ' CACHE ' || mark_seq_rec.sequ_cache_val;
    END IF;
    IF curr_seq_rec.is_cycled <> mark_seq_rec.sequ_is_cycled THEN
      IF mark_seq_rec.sequ_is_cycled = 'f' THEN
        v_stmt=v_stmt || ' NO ';
      END IF;
      v_stmt=v_stmt || ' CYCLE ';
    END IF;
-- and execute the statement if at least one parameter has changed
    IF v_stmt <> '' THEN
      RAISE NOTICE 'Rollback sequence % with%', v_fullSeqName, v_stmt;
      EXECUTE 'ALTER SEQUENCE ' || v_fullSeqName || v_stmt;
    END IF;
-- delete the rollbacked sequence from the sequence table
    DELETE FROM emaj.emaj_sequence 
        WHERE sequ_schema = v_schemaName AND sequ_name = v_seqName AND sequ_datetime >= v_timestamp;
-- insert event in history
    INSERT INTO emaj.emaj_hist (oper_type, oper_object, oper_wording) 
      VALUES ('ROLLBACK_SEQUENCE', v_fullSeqName, SUBSTR(v_stmt,2));
    RETURN;
  END;
$emaj_rlbk_sequence$ LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION emaj.emaj_verify_all() RETURNS void AS 
$emaj_verify_all$
-- The function verifies the consistency between log and application tables
-- It generates an error if the check fails
  DECLARE
    v_emajSchema     TEXT := 'emaj';
    v_fullTableName  TEXT;
    v_logTableName   TEXT;
    v_logFnctName    TEXT;
    v_rlbkFnctName   TEXT;
    v_triggerName    TEXT;
    v_relkind        TEXT;
    r_tblsq          RECORD;
  BEGIN
-- verify that no table or sequence belongs to several groups
    BEGIN
      PERFORM group_schema, group_table, count(*) FROM emaj.emaj_group
        GROUP BY group_schema, group_table HAVING count(*) > 1;
      IF FOUND THEN
        RAISE EXCEPTION 'emaj_verify_all: At least one table is referenced by several groups';
      END IF;
    END;
-- verify that all log tables correspond to a row in the groups table
    BEGIN
      PERFORM relname FROM pg_class, pg_namespace WHERE 
        relnamespace = pg_namespace.oid AND nspname = 'emaj' AND relkind = 'r' AND relname LIKE '%_log'
      EXCEPT 
      SELECT group_schema || '_' || group_table || '_log' FROM emaj.emaj_group;
      IF FOUND THEN
        RAISE EXCEPTION 'emaj_verify_all: At least one log table is not linked to an application table though the emaj_group table';
      END IF;
    END;
-- per table verifications
    FOR r_tblsq IN
        SELECT group_schema, group_table FROM emaj.emaj_group
        LOOP
-- check the class is valid
      v_relkind = emaj.emaj_check_class(r_tblsq.group_schema, r_tblsq.group_table);
      IF v_relkind = 'r' THEN
-- if it is a table, ...
        v_logTableName   := r_tblsq.group_schema || '_' || r_tblsq.group_table || '_log';
        v_logFnctName    := r_tblsq.group_schema || '_' || r_tblsq.group_table || '_log_fnct';
        v_rlbkFnctName   := r_tblsq.group_schema || '_' || r_tblsq.group_table || '_rlbk_fnct';
        v_triggerName    := r_tblsq.group_schema || '_' || r_tblsq.group_table || '_emaj_trg';
        v_fullTableName  := quote_ident(r_tblsq.group_schema) || '.' || quote_ident(r_tblsq.group_table);
--   -> check the log table exists
        PERFORM relname FROM pg_class, pg_namespace WHERE 
          relnamespace = pg_namespace.oid AND nspname = 'emaj' AND relkind = 'r' AND relname = v_logTableName;
        IF NOT FOUND THEN
          RAISE EXCEPTION 'emaj_verify_all: Log table % not found',v_logTableName;
        END IF;
--   -> check boths functions exists
        PERFORM proname from pg_proc , pg_namespace WHERE 
          pronamespace = pg_namespace.oid AND nspname = 'emaj' AND proname = v_logFnctName;
        IF NOT FOUND THEN
          RAISE EXCEPTION 'emaj_verify_all: Log function % not found',v_logFnctName;
        END IF;
        PERFORM proname from pg_proc , pg_namespace WHERE 
          pronamespace = pg_namespace.oid AND nspname = 'emaj' AND proname = v_rlbkFnctName;
        IF NOT FOUND THEN
          RAISE EXCEPTION 'emaj_verify_all: Restore function % not found',v_rlbkFnctName;
        END IF;
--   -> check the trigger exists
        PERFORM tgname FROM pg_trigger WHERE tgname = v_triggerName;
        IF NOT FOUND THEN
          RAISE EXCEPTION 'emaj_verify_all: Trigger % not found',v_triggerName;
        END IF;
--   -> check that the log tables structure is consistent with the application tables structure
--      (same columns and same formats)
        PERFORM attname, atttypid, attlen, atttypmod FROM pg_attribute, pg_class, pg_namespace 
          WHERE nspname = r_tblsq.group_schema AND relnamespace = pg_namespace.oid AND relname = r_tblsq.group_table
            AND attrelid = pg_class.oid AND attnum > 0 AND attisdropped = false
        EXCEPT
        SELECT attname, atttypid, attlen, atttypmod FROM pg_attribute, pg_class, pg_namespace
          WHERE nspname = v_emajSchema AND relnamespace = pg_namespace.oid AND relname = v_logTableName
            AND attrelid = pg_class.oid AND attnum > 0 AND attisdropped = false AND attname NOT LIKE 'emaj%';
        IF FOUND THEN
          RAISE EXCEPTION 'emaj_verify_all: The structure of log table % is not coherent with %' ,v_logTableName,v_fullTableName;
        END IF;
        ELSEIF v_relkind = 'S' THEN
-- if it is a sequence, nothing to do
      END IF;
    END LOOP;
    RETURN;
  END;
$emaj_verify_all$ LANGUAGE plpgsql;

------------------------------------------------
----                                        ----
----       Functions to manage groups       ----
----                                        ----
------------------------------------------------

CREATE or REPLACE FUNCTION emaj.emaj_lock_group(v_groupName TEXT) RETURNS void AS 
$emaj_lock_group$
-- This function locks all tables of a group
-- Input: group name
  DECLARE
    v_relkind       TEXT;
    v_fullTableName TEXT;
    r_tblsq         RECORD;
  BEGIN
-- insert begin in the history
    INSERT INTO emaj.emaj_hist (oper_type, oper_object, oper_wording) 
      VALUES ('LOCK_GROUP BEGIN', v_groupName, '');
-- scan all classes of the group
    FOR r_tblsq IN
        SELECT group_schema, group_table FROM emaj.emaj_group WHERE group_name = v_groupName
        LOOP
-- check the class is valid
      v_relkind = emaj.emaj_check_class(r_tblsq.group_schema, r_tblsq.group_table);
      IF v_relkind = 'r' THEN
-- if it is a table, lock the table
        v_fullTableName := quote_ident(r_tblsq.group_schema) || '.' || quote_ident(r_tblsq.group_table);
        EXECUTE 'LOCK TABLE ' || v_fullTableName;
      END IF;
    END LOOP;
-- insert end in the history
    INSERT INTO emaj.emaj_hist (oper_type, oper_object, oper_wording) 
      VALUES ('LOCK_GROUP END', v_groupName, '');
    RETURN;
  END;
$emaj_lock_group$ LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION emaj.emaj_create_group(v_groupName TEXT) RETURNS INT AS 
$emaj_create_group$
-- This function creates emaj objects for all tables of a group
-- Input: group name
-- Output: number of processed tables and sequences
  DECLARE
    v_pgversion     TEXT := substring (version() from E'PostgreSQL\\s(\\d+\\.\\d+)\\.');
    v_nb_tbl        INT := 0;
    v_relkind       TEXT;
    v_stmt          TEXT;
    v_nb_trg        INT;
    r_tblsq         RECORD;
  BEGIN
-- insert begin in the history
    INSERT INTO emaj.emaj_hist (oper_type, oper_object, oper_wording) 
      VALUES ('CREATE_GROUP BEGIN', v_groupName, '');
-- check that no table of the group has an activated log trigger
    v_stmt = 'SELECT count(tgname) FROM pg_trigger, emaj.emaj_group ' ||
             'WHERE group_name = ''' || v_groupName || '''' ||
             ' AND tgname = group_schema || ''_'' || group_table || ''_emaj_trg'' ' || 
             ' AND tgenabled ';
    IF v_pgversion <= '8.2' THEN
       v_stmt = v_stmt || '= true';
    ELSE
       v_stmt = v_stmt || '!= ''D''';
    END IF;
    EXECUTE v_stmt INTO v_nb_trg;
    IF v_nb_trg > 0 THEN
      RAISE EXCEPTION 'emaj_create_group: At least one table of the group % has an enabled log trigger', v_groupName;
    END IF;
-- OK, scan all classes of the group
    FOR r_tblsq IN
        SELECT group_schema, group_table FROM emaj.emaj_group WHERE group_name = v_groupName
        LOOP
-- check the class is valid
      v_relkind = emaj.emaj_check_class(r_tblsq.group_schema, r_tblsq.group_table);
      IF v_relkind = 'r' THEN
-- if it is a table, create the related emaj objects
         PERFORM emaj.emaj_create_log (r_tblsq.group_schema, r_tblsq.group_table);
        ELSEIF v_relkind = 'S' THEN
-- if it is a sequence, nothing to do
      END IF;
      v_nb_tbl = v_nb_tbl + 1;
    END LOOP;
    IF v_nb_tbl = 0 THEN
       RAISE EXCEPTION 'emaj_create_group: Group % is unknown', v_groupName;
    END IF;
-- insert end in the history
    INSERT INTO emaj.emaj_hist (oper_type, oper_object, oper_wording) 
      VALUES ('CREATE_GROUP END', v_groupName, v_nb_tbl || ' tables/sequences processed');
    RETURN v_nb_tbl;
  END;
$emaj_create_group$ LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION emaj.emaj_delete_group(v_groupName TEXT) RETURNS INT AS 
$emaj_delete_group$
-- This function deletes the emaj objects for all tables of a group
-- Input: group name
-- Output: number of processed tables and sequences
  DECLARE
    v_nb_tbl        INT := 0;
    v_relkind       TEXT;
    r_tblsq         RECORD;
  BEGIN
-- insert begin in the history
    INSERT INTO emaj.emaj_hist (oper_type, oper_object, oper_wording) 
      VALUES ('DELETE_GROUP BEGIN', v_groupName, '');
-- check that the group is not in a LOGGING state
    PERFORM oper_datetime FROM emaj.emaj_hist
      WHERE oper_type = 'emaj_START' AND 
            oper_datetime > (SELECT MAX(oper_datetime) FROM emaj.emaj_hist WHERE oper_type = 'emaj_STOP');
    IF FOUND THEN
      RAISE EXCEPTION 'emaj_delete_group: The group % is in logging state', v_groupName;
    END IF;
-- OK, delete the emaj objets for each table of the group
    FOR r_tblsq IN
        SELECT group_schema, group_table FROM emaj.emaj_group WHERE group_name = v_groupName
        LOOP
-- check the class is valid
      v_relkind = emaj.emaj_check_class(r_tblsq.group_schema, r_tblsq.group_table);
      IF v_relkind = 'r' THEN
-- if it is a table, delete the related emaj objects
        PERFORM emaj.emaj_delete_log (r_tblsq.group_schema, r_tblsq.group_table);
        ELSEIF v_relkind = 'S' THEN
-- if it is a sequence, delete all related data from emaj_sequence table
          PERFORM emaj.emaj_delete_seq (r_tblsq.group_schema, r_tblsq.group_table);
      END IF;
      v_nb_tbl = v_nb_tbl + 1;
    END LOOP;
    IF v_nb_tbl = 0 THEN
       RAISE EXCEPTION 'emaj_delete_group: Group % is unknown', v_groupName;
    END IF;
-- insert end in the history
    INSERT INTO emaj.emaj_hist (oper_type, oper_object, oper_wording) 
      VALUES ('DELETE_GROUP END', v_groupName, v_nb_tbl || ' tables/sequences processed');
    RETURN v_nb_tbl;
  END;
$emaj_delete_group$ LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION emaj.emaj_start_group(v_groupName TEXT, v_mark TEXT) RETURNS INT AS 
$emaj_start_group$
-- This function activates the log triggers of all the tables for a group and set a first mark
-- Input: group name, name of the mark to set
-- Output: number of processed tables
  DECLARE
    v_nb_tbl        INT := 0;
    v_logTableName  TEXT;
    v_fullTableName TEXT;
    v_triggerName   TEXT;
    v_relkind       TEXT;
    v_cpt           BIGINT;
    r_tblsq         RECORD;
  BEGIN
-- insert begin in the history
    INSERT INTO emaj.emaj_hist (oper_type, oper_object, oper_wording) 
      VALUES ('START_GROUP BEGIN', v_groupName, '');
-- check if the emaj environment is OK
    PERFORM emaj.emaj_verify_all();
-- check there is no remaining mark for the group 
    PERFORM 1 FROM emaj.emaj_mark WHERE mark_group = v_groupName;
    IF found THEN
      RAISE EXCEPTION 'emaj_start_group: remaining rows in emaj.emaj_mark table for group %',v_groupName;
    END IF;
-- check the log tables are empty
    FOR r_tblsq IN
        SELECT group_schema, group_table FROM emaj.emaj_group WHERE group_name = v_groupName
        LOOP
-- check the class is valid
      v_relkind = emaj.emaj_check_class(r_tblsq.group_schema, r_tblsq.group_table);
      IF v_relkind = 'r' THEN
-- if it is a table, verify the log table is empty
        v_logTableName   := quote_ident(r_tblsq.group_schema || '_' || r_tblsq.group_table || '_log');
        EXECUTE 'SELECT 1 FROM emaj.' || v_logTableName INTO v_cpt;
        IF v_cpt IS NOT NULL THEN
          RAISE EXCEPTION 'emaj_start_group: log table % is not empty', v_logTableName;
        END IF;
        ELSEIF v_relkind = 'S' THEN
-- check emaj_sequence tables doesn't contain remaining rows for the sequence
        PERFORM 1 FROM emaj.emaj_sequence where sequ_schema = r_tblsq.group_schema AND sequ_name = r_tblsq.group_table;
        IF found THEN
          RAISE EXCEPTION 'emaj_start_group: remaining rows in emaj.emaj_sequence for %.%',
                           r_tblsq.group_schema,r_tblsq.group_table;
        END IF;
      END IF;
      v_nb_tbl = v_nb_tbl + 1;
    END LOOP;
    IF v_nb_tbl = 0 THEN
       RAISE EXCEPTION 'emaj_start_group: Group % is unknown', v_groupName;
    END IF;
   v_nb_tbl = 0;
-- OK, begin a sub-transaction, lock all tables to get a stable point ...
    BEGIN
      PERFORM emaj.emaj_lock_group(v_groupName);
-- ... and enable all log triggers for the group
      FOR r_tblsq IN
          SELECT group_schema, group_table FROM emaj.emaj_group WHERE group_name = v_groupName
          LOOP
        v_relkind = emaj.emaj_check_class(r_tblsq.group_schema, r_tblsq.group_table);
        IF v_relkind = 'r' THEN
-- if it is a table, enable the emaj log trigger
          v_fullTableName := quote_ident(r_tblsq.group_schema) || '.' || quote_ident(r_tblsq.group_table);
          v_triggerName    := quote_ident(r_tblsq.group_schema || '_' || r_tblsq.group_table || '_emaj_trg');
          EXECUTE 'ALTER TABLE ' || v_fullTableName || ' ENABLE TRIGGER ' || v_triggerName;
          ELSEIF v_relkind = 'S' THEN
-- if it is a sequence, nothing to do
        END IF;
        v_nb_tbl = v_nb_tbl + 1;
      END LOOP;
-- insert end in the history
      INSERT INTO emaj.emaj_hist (oper_type, oper_object, oper_wording) 
        VALUES ('START_GROUP END', v_groupName, v_nb_tbl || ' tables/sequences processed');
-- Set the first mark
      PERFORM emaj.emaj_set_mark_group(v_groupName, v_mark);
-- ... and finaly commit the operation and release locks
    END;
-- ... just before ending
    RETURN v_nb_tbl;
  END;
$emaj_start_group$ LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION emaj.emaj_set_mark_group(v_groupName TEXT, v_mark TEXT) RETURNS void AS
$emaj_set_mark_group$
-- This function inserts a mark in the emaj_mark table and takes an image of the sequences definitions for the group
-- Input: group name, mark to set
  DECLARE
    v_pgversion     TEXT := substring (version() from E'PostgreSQL\\s(\\d+\\.\\d+)\\.');
    v_emajSchema    TEXT := 'emaj';
    v_flg           INT  := 0;
    v_nb_tbl        INT  := 0;
    v_relkind       TEXT;
    v_fullSeqName   TEXT;
    v_timestamp     TIMESTAMPTZ;
    v_stmt          TEXT;
    r_tblsq         RECORD;
  BEGIN
-- insert begin in the history
    INSERT INTO emaj.emaj_hist (oper_type, oper_object, oper_wording) 
      VALUES ('SET_MARK_GROUP BEGIN', v_groupName, v_mark);
-- check if the emaj environment is OK
    PERFORM emaj.emaj_verify_all();
-- if a mark with the same name already exists for the group, stop
    SELECT 1 INTO v_flg FROM emaj.emaj_mark
      WHERE mark_group = v_groupName AND mark_name = v_mark LIMIT 1;
    IF v_flg > 0 THEN
       RAISE EXCEPTION 'emaj_set_mark_group: A mark has been already set with the same name (%) for this group (%).', v_mark, v_groupName;
    END IF;
-- OK, begin a sub-transaction, lock all tables to get a stable point ...
    BEGIN
      PERFORM emaj.emaj_lock_group(v_groupName);
-- ... look at the clock and insert the mark into the emaj_mark table
      v_timestamp = clock_timestamp();
      INSERT INTO emaj.emaj_mark (mark_datetime, mark_group, mark_name) 
        VALUES (v_timestamp, v_groupName, v_mark);
-- then, examine the group's definition
      FOR r_tblsq IN
          SELECT group_schema, group_table FROM emaj.emaj_group WHERE group_name = v_groupName
          LOOP
-- check the class is valid
        v_relkind = emaj.emaj_check_class(r_tblsq.group_schema, r_tblsq.group_table);
        IF v_relkind = 'r' THEN
-- if it is a table, record the emaj_id associated sequence parameters in the emaj sequence table
          v_fullSeqName := quote_ident(r_tblsq.group_schema || '_' || r_tblsq.group_table || '_log_emaj_id_seq');
          v_stmt = 'INSERT INTO emaj.emaj_sequence (' ||
                   'sequ_schema, sequ_name, sequ_datetime, sequ_mark, sequ_last_val, sequ_start_val, ' || 
                   'sequ_increment, sequ_max_val, sequ_min_val, sequ_cache_val, sequ_is_cycled, sequ_is_called ' ||
                   ') SELECT '''|| v_emajSchema || ''', ''' || v_fullSeqName || ''', ''' || v_timestamp || ''', ''' || v_mark || 
                   ''', ' || 'last_value, ';
          IF v_pgversion <= '8.3' THEN
             v_stmt = v_stmt || '0, ';
          ELSE
             v_stmt = v_stmt || 'start_value, ';
          END IF;
          v_stmt = v_stmt || 
                   'increment_by, max_value, min_value, cache_value, is_cycled, is_called ' ||
                   'FROM emaj.' || v_fullSeqName;
          EXECUTE v_stmt;
        ELSEIF v_relkind = 'S' THEN
-- if it is a sequence, record the sequence parameters in the emaj sequence table
          v_fullSeqName := quote_ident(r_tblsq.group_schema) || '.' || quote_ident(r_tblsq.group_table);
          v_stmt = 'INSERT INTO emaj.emaj_sequence (' ||
                   'sequ_schema, sequ_name, sequ_datetime, sequ_mark, sequ_last_val, sequ_start_val, ' || 
                   'sequ_increment, sequ_max_val, sequ_min_val, sequ_cache_val, sequ_is_cycled, sequ_is_called ' ||
                   ') SELECT ''' || r_tblsq.group_schema || ''', ''' || 
                   r_tblsq.group_table || ''', ''' || v_timestamp || ''', ''' || v_mark || ''', ' ||
                   'last_value, ';
          IF v_pgversion <= '8.3' THEN
             v_stmt = v_stmt || '0, ';
          ELSE
             v_stmt = v_stmt || 'start_value, ';
          END IF;
          v_stmt = v_stmt || 
                   'increment_by, max_value, min_value, cache_value, is_cycled, is_called ' ||
                   'FROM ' || v_fullSeqName;
          EXECUTE v_stmt;
        END IF;
        v_nb_tbl = v_nb_tbl + 1;
      END LOOP;
      IF v_nb_tbl = 0 THEN
         RAISE EXCEPTION 'emaj_set_mark_group: Group % is unknown', v_groupName;
      END IF;
-- ... and finaly commit the operation and release locks
    END;
-- insert end in the history
    INSERT INTO emaj.emaj_hist (oper_type, oper_object, oper_wording) 
      VALUES ('SET_MARK_GROUP END', v_groupName, v_mark);
    RETURN;
  END;
$emaj_set_mark_group$ LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION emaj.emaj_delete_mark_group(v_groupName TEXT, v_mark TEXT) RETURNS void AS
$emaj_delete_mark_group$
-- This function deletes all traces from a previous set_mark_group function. 
-- Then, any rollback on the deleted mark will be possible.
-- It affects emaj_mark and emaj_sequence tables. 
-- The initial mark set at start_group time cannot be deleted.
-- Input: group name, mark to delete
  DECLARE
    v_emajSchema    TEXT := 'emaj';
    v_firstMark     TEXT;
    v_timestamp     TIMESTAMPTZ;
    v_nb_tbl        INT := 0;
    v_relkind       TEXT;
    v_fullSeqName   TEXT;
    r_tblsq         RECORD;
  BEGIN
-- check if the mark is not the first mark for the group
    SELECT mark_name INTO v_firstMark
      FROM emaj.emaj_mark
      WHERE mark_group = v_groupName 
        AND mark_datetime = (SELECT min (mark_datetime) FROM emaj.emaj_mark WHERE mark_group = v_groupName);
    IF v_mark = v_firstMark THEN
       RAISE EXCEPTION 'emaj_delete_mark_group: The first mark (%) of the group cannot be deleted.', v_mark;
    END IF;
-- OK
    BEGIN
-- catch the mark's timestamp and then delete the mark from the emaj_mark table
      SELECT mark_datetime INTO v_timestamp 
        FROM emaj.emaj_mark 
        WHERE mark_group = v_groupName AND mark_name = v_mark;
      DELETE FROM emaj.emaj_mark 
        WHERE mark_group = v_groupName AND mark_name = v_mark;
-- then, examine the group's definition
      FOR r_tblsq IN
          SELECT group_schema, group_table FROM emaj.emaj_group WHERE group_name = v_groupName
          LOOP
-- check the class is valid
        v_relkind = emaj.emaj_check_class(r_tblsq.group_schema, r_tblsq.group_table);
        IF v_relkind = 'r' THEN
-- if it is a table, record the emaj_id associated sequence parameters in the emaj sequence table
          v_fullSeqName := quote_ident(r_tblsq.group_schema || '_' || r_tblsq.group_table || '_log_emaj_id_seq');
          DELETE FROM emaj.emaj_sequence 
            WHERE sequ_schema = 'emaj' AND sequ_name = v_fullSeqName AND sequ_datetime = v_timestamp;
        ELSEIF v_relkind = 'S' THEN
-- if it is a sequence, record the sequence parameters in the emaj sequence table
--          v_fullSeqName := quote_ident(r_tblsq.group_schema) || '.' || quote_ident(r_tblsq.group_table);
          DELETE FROM emaj.emaj_sequence 
            WHERE sequ_schema = quote_ident(r_tblsq.group_schema) AND sequ_name = quote_ident(r_tblsq.group_table)
              AND sequ_datetime = v_timestamp;
        END IF;
        v_nb_tbl = v_nb_tbl + 1;
      END LOOP;
      IF v_nb_tbl = 0 THEN
         RAISE EXCEPTION 'emaj_delete_mark_group: Group % is unknown', v_groupName;
      END IF;
    END;
-- insert in the history
    INSERT INTO emaj.emaj_hist (oper_type, oper_object, oper_wording) 
      VALUES ('DELETE_MARK_GROUP', v_groupName, v_mark);
    RETURN;
  END;
$emaj_delete_mark_group$ LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION emaj.emaj_stop_group(v_groupName TEXT) RETURNS INT AS 
$emaj_stop_group$
-- This function de-activates the log triggers of all the tables for a group
-- Input: group name
-- Output: number of processed tables and sequences
  DECLARE
    v_nb_tbl        INT := 0;
    v_fullTableName TEXT;
    v_triggerName   TEXT;
    v_relkind       TEXT;
    r_tblsq         RECORD;
  BEGIN
-- insert begin in the history
    INSERT INTO emaj.emaj_hist (oper_type, oper_object, oper_wording) 
      VALUES ('STOP_GROUP BEGIN', v_groupName, '');
    FOR r_tblsq IN
        SELECT group_schema, group_table FROM emaj.emaj_group WHERE group_name = v_groupName
        LOOP
-- check the class is valid
      v_relkind = emaj.emaj_check_class(r_tblsq.group_schema, r_tblsq.group_table);
      IF v_relkind = 'r' THEN
-- if it is a table, disable the emaj log trigger
        v_fullTableName := quote_ident(r_tblsq.group_schema) || '.' || quote_ident(r_tblsq.group_table);
        v_triggerName    := quote_ident(r_tblsq.group_schema || '_' || r_tblsq.group_table || '_emaj_trg');
        EXECUTE 'ALTER TABLE ' || v_fullTableName || ' DISABLE TRIGGER ' || v_triggerName;
        ELSEIF v_relkind = 'S' THEN
-- if it is a sequence, nothing to do
      END IF;
      v_nb_tbl = v_nb_tbl + 1;
    END LOOP;
    IF v_nb_tbl = 0 THEN
       RAISE EXCEPTION 'emaj_stop_group: Group % is unknown', v_groupName;
    END IF;
-- delete all marks for the group from the emaj_mark table to avoid any rollback
    DELETE FROM emaj.emaj_mark WHERE mark_group = v_groupName; 
-- insert end in the history
    INSERT INTO emaj.emaj_hist (oper_type, oper_object, oper_wording) 
      VALUES ('STOP_GROUP END', v_groupName, v_nb_tbl || ' tables/sequences processed');
    RETURN v_nb_tbl;
  END;
$emaj_stop_group$ LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION emaj.emaj_rollback_group(v_groupName TEXT, v_mark TEXT) RETURNS INT AS
$emaj_rollback_group$
-- The function rollbacks all tables ans sequences of a group up to a mark in the history
-- Input: group name, mark in the history, as it is inserted by emaj.emaj_set_mark_group
-- Output: number of processed tables and sequences
  DECLARE
    v_nb_tbl            INT := 0;
    v_nb_tbl_in_group   INT;
    v_relkind           TEXT;
    v_timestampMark     TIMESTAMPTZ;
    r_tblsq             RECORD;
  BEGIN
-- check that emaj environment is OK 
    PERFORM emaj.emaj_verify_all();
-- check the requested group exists
    SELECT count(*) INTO v_nb_tbl_in_group FROM emaj.emaj_group
      WHERE group_name = v_groupName;
    IF v_nb_tbl_in_group = 0 THEN
       RAISE EXCEPTION 'emaj_rollback_group: Group % is unknown', v_groupName;
       RETURN 0;
    END IF;
-- check the requested mark exists
    SELECT MAX(mark_datetime) INTO v_timestampMark FROM emaj.emaj_mark
      WHERE mark_group = v_groupName AND mark_name = v_mark;
    IF v_timestampMark IS NULL THEN
       RAISE EXCEPTION 'emaj_rollback_group: No mark % for group % ', v_mark, v_groupName;
       RETURN 0;
    END IF;
-- insert begin in the history
    INSERT INTO emaj.emaj_hist (oper_type, oper_object, oper_wording) 
      VALUES ('ROLLBACK_GROUP BEGIN', v_groupName, 'Rollback to mark ' || v_mark || ' [' || v_timestampMark || ']');
-- OK, begin a sub-transaction, lock all tables.
    BEGIN
      PERFORM emaj.emaj_lock_group(v_groupName);
-- and rollback each table and sequence
      FOR r_tblsq IN
          SELECT group_schema, group_table FROM emaj.emaj_group WHERE group_name = v_groupName
          LOOP
-- check the class is valid
        v_relkind = emaj.emaj_check_class(r_tblsq.group_schema, r_tblsq.group_table);
        IF v_relkind = 'r' THEN
-- if it is a table, rollback for this table
          PERFORM emaj.emaj_rlbk_table(r_tblsq.group_schema, r_tblsq.group_table, v_timestampMark);
          ELSEIF v_relkind = 'S' THEN
-- if it is a sequence, rollback for this sequence
          PERFORM emaj.emaj_rlbk_sequence(r_tblsq.group_schema, r_tblsq.group_table, v_timestampMark);
        END IF;
        v_nb_tbl = v_nb_tbl + 1;
      END LOOP;
-- final check that we have the expected number of processed tables and sequences
      IF v_nb_tbl = 0 or v_nb_tbl <> v_nb_tbl_in_group THEN
         RAISE EXCEPTION 'emaj_rlbk_group: Internal error 1';
      END IF;
-- delete the now rollbacked marks
      DELETE FROM emaj.emaj_mark WHERE mark_datetime >= v_timestampMark;
    END;
-- insert end in the history
    INSERT INTO emaj.emaj_hist (oper_type, oper_object, oper_wording) 
      VALUES ('ROLLBACK_GROUP END', v_groupName, v_nb_tbl || ' tables/sequences processed');
    RETURN v_nb_tbl;
  END;
$emaj_rollback_group$ LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION emaj.emaj_reset_group(v_groupName TEXT) RETURNS INT AS 
$emaj_reset_group$
-- This function empties the log tables for all tables of a group, using a TRUNCATE, and deletes the sequences saves
-- Input: group name
-- Output: number of processed tables
  DECLARE
    v_nb_tbl        INT := 0;
    v_emajSchema    TEXT := 'emaj';
    v_logTableName  TEXT;
    v_fullSeqName   TEXT;
    v_relkind       TEXT;
    r_tblsq         RECORD;
  BEGIN
-- insert begin in the history
    INSERT INTO emaj.emaj_hist (oper_type, oper_object, oper_wording) 
      VALUES ('RESET_GROUP BEGIN', v_groupName, '');
-- check the group is not in a logging state
    PERFORM oper_datetime FROM emaj.emaj_hist
      WHERE oper_type = 'START_GROUP' AND 
            oper_datetime > (SELECT MAX(oper_datetime) FROM emaj.emaj_hist WHERE oper_type = 'STOP_GROUP');
    IF FOUND THEN
      RAISE EXCEPTION 'emaj_reset_group: The groupe % is currently logging', v_groupName;
    END IF;
-- delete all marks for the group from the emaj_mark table to avoid any rollback
    DELETE FROM emaj.emaj_mark WHERE mark_group = v_groupName; 
-- then, truncate log tables
    FOR r_tblsq IN
        SELECT group_schema, group_table FROM emaj.emaj_group WHERE group_name = v_groupName
        LOOP
-- check the class is valid
      v_relkind = emaj.emaj_check_class(r_tblsq.group_schema, r_tblsq.group_table);
      IF v_relkind = 'r' THEN
-- if it is a table, 
--   truncate the related log table
        v_logTableName := quote_ident(v_emajSchema) || '.' || quote_ident(r_tblsq.group_schema || '_' || r_tblsq.group_table || '_log');
        EXECUTE 'TRUNCATE ' || v_logTableName;
--   and delete rows from emaj_sequence related to the associated emaj_id sequence
        v_fullSeqName := quote_ident(r_tblsq.group_schema || '_' || r_tblsq.group_table || '_log_emaj_id_seq');
        DELETE FROM emaj.emaj_sequence WHERE sequ_name = v_fullSeqName;
      ELSEIF v_relkind = 'S' THEN
-- if it is a sequence, delete all related data from emaj_sequence table
        PERFORM emaj.emaj_delete_seq (r_tblsq.group_schema, r_tblsq.group_table);
      END IF;
      v_nb_tbl = v_nb_tbl + 1;
    END LOOP;
    IF v_nb_tbl = 0 THEN
       RAISE EXCEPTION 'emaj_reset_group: Group % is unknown', v_groupName;
    END IF;
-- insert end in the history
    INSERT INTO emaj.emaj_hist (oper_type, oper_object, oper_wording) 
      VALUES ('RESET_GROUP END', v_groupName, v_nb_tbl || ' tables/sequences processed');
    RETURN v_nb_tbl;
  END;
$emaj_reset_group$ LANGUAGE plpgsql;

CREATE TYPE emaj.emaj_stat_type AS (
    stat_group     TEXT,
    stat_schema    TEXT,
    stat_table     TEXT,
    stat_role      VARCHAR(32),
    stat_verb      VARCHAR(3),
    stat_rows      BIGINT
    );

CREATE or REPLACE FUNCTION emaj.emaj_log_stat_group(v_groupName TEXT, v_firstMark TEXT, v_lastMark TEXT) RETURNS SETOF emaj.emaj_stat_type AS 
$emaj_log_stat_group$
-- This function returns statistics on row updates executed between 2 marks as viewed through the log tables
-- Input: group name, the 2 marks names defining a range (either one or both marks can be NULL)
-- Output: table of updates by user and table
  DECLARE
    v_nb_tbl        INT := 0;
    v_emajSchema    TEXT := 'emaj';
    v_logTableName  TEXT;
    v_fullSeqName   TEXT;
    v_relkind       TEXT;
    v_tsFirstMark   TIMESTAMPTZ;
    v_tsLastMark    TIMESTAMPTZ;
    v_stmt          TEXT;
    r_tblsq         RECORD;
    r_stat          RECORD;
  BEGIN
-- catch the timestamp of the first mark
    IF v_firstMark IS NOT NULL THEN
       SELECT mark_datetime INTO v_tsFirstMark
         FROM emaj.emaj_mark
         WHERE mark_group = v_groupName AND mark_name = v_firstMark;
       IF NOT FOUND THEN
          RAISE EXCEPTION 'emaj_log_stat_group: Start mark % is unknown for group %', v_firstMark, v_groupName;
       END IF;
    END IF;
-- catch the timestamp of the last mark
    IF v_lastMark IS NOT NULL THEN
       SELECT mark_datetime INTO v_tsLastMark
         FROM emaj.emaj_mark
         WHERE mark_group = v_groupName AND mark_name = v_lastMark;
       IF NOT FOUND THEN
          RAISE EXCEPTION 'emaj_log_stat_group: End mark % is unknown for group %', v_lastMark, v_groupName;
       END IF;
    END IF;
-- for each table of the emaj_group table
    FOR r_tblsq IN
        SELECT group_schema, group_table FROM emaj.emaj_group WHERE group_name = v_groupName
        LOOP
-- check the class is valid
      v_relkind = emaj.emaj_check_class(r_tblsq.group_schema, r_tblsq.group_table);
      IF v_relkind = 'r' THEN
-- if it is a table, count the number of operation per type (INSERT, UPDATE and DELETE) and role
        v_logTableName := quote_ident(v_emajSchema) || '.' || quote_ident(r_tblsq.group_schema || '_' || r_tblsq.group_table || '_log');
        v_stmt= 'SELECT ''' || v_groupName || '''::TEXT as emaj_group, ''' || r_tblsq.group_schema ;
        v_stmt= v_stmt || '''::TEXT as emaj_schema, '''|| r_tblsq.group_table || '''::TEXT as emaj_table,';
        v_stmt= v_stmt || ' emaj_user, emaj_verb, count(*) as emaj_rows FROM ' || v_logTableName ;
        v_stmt= v_stmt || ' WHERE NOT (emaj_verb = ''UPD'' AND emaj_tuple = ''OLD'')';
        IF v_firstMark IS NOT NULL THEN
          v_stmt= v_stmt || ' AND emaj_changed >= timestamp '''|| v_tsFirstMark || '''';
        END IF;
        IF v_lastMark IS NOT NULL THEN
          v_stmt= v_stmt || ' AND emaj_changed < timestamp '''|| v_tsLastMark || '''';
        END IF;
        v_stmt= v_stmt || ' GROUP BY emaj_group, emaj_schema, emaj_table, emaj_user, emaj_verb';
        v_stmt= v_stmt || ' ORDER BY emaj_user, emaj_verb';
        FOR r_stat IN EXECUTE v_stmt LOOP
          RETURN NEXT r_stat;
        END LOOP;
      END IF;
      v_nb_tbl = v_nb_tbl + 1;
    END LOOP;
    IF v_nb_tbl = 0 THEN
       RAISE EXCEPTION 'emaj_log_stat_group: Group % is unknown', v_groupName;
    END IF;
    RETURN;
  END;
$emaj_log_stat_group$ LANGUAGE plpgsql;

-- and insert the init record in the operation history
INSERT INTO emaj.emaj_hist (oper_type,oper_wording) VALUES ('EMAJ_INIT','E-maj initialisation completed');

COMMIT;