-- PROCEDURE: aws_commons.presetsequence(character varying, character varying)

-- DROP PROCEDURE IF EXISTS aws_commons.presetsequence(character varying, character varying);

CREATE OR REPLACE PROCEDURE aws_commons.presetsequence(
	par_dbname character varying,
	par_schema character varying,
	par_table  character varying,
	par_table2 character varying DEFAULT null,
	par_table3 character varying DEFAULT null,
	par_table4 character varying DEFAULT null,
	par_table5 character varying DEFAULT null,
	par_table6 character varying DEFAULT null,
	par_table7 character varying DEFAULT null,
	par_table8 character varying DEFAULT null,
	par_table9 character varying DEFAULT null)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
	var_table_pk character varying;
	var_db_table character varying;
	var_max_id bigint;
	var_list record;
	var_table character varying;
BEGIN
IF par_dbname IS NULL OR par_dbname = '' 
	THEN par_dbname = DB_NAME();
END IF;
IF par_schema IS NULL OR par_schema = '' 
	THEN par_schema = 'dbo';
END IF;

FOR var_list IN
	SELECT a FROM (VALUES(par_table),(par_table2),(par_table3),(par_table4),(par_table5),(par_table6),(par_table7),(par_table8),(par_table9)) s(a)
LOOP
	var_table = var_list.a;
-- 	raise notice 'var_table: %',var_table;

	IF var_table IS NOT NULL
	THEN
		var_db_table = concat(par_schema,'.',var_table);	
		
		/* Get Primary key */
		SELECT a.attname
		into var_table_pk
		FROM   pg_index i
		JOIN   pg_attribute a ON a.attrelid = i.indrelid
							 AND a.attnum = ANY(i.indkey)
		WHERE  i.indrelid = var_db_table::regclass
		AND    i.indisprimary;
-- 		RAISE NOTICE 'var_db_table: %',var_db_table;
-- 		RAISE NOTICE 'var_table_pk: %',var_table_pk;

	/* GET TABLE MAX ID */
		EXECUTE 'SELECT MAX("'||var_table_pk||'") FROM '||var_db_table INTO var_max_id;
-- 		raise notice 'var_max_id: %',var_max_id;

		/* SET MAX ID AS SEQUENCE NUMBER */
		perform setval(concat(var_db_table,'sequence')::regclass,var_max_id , true);
	END IF;
end loop;

END;
$BODY$;
