ALTER FUNCTION public.spRemoveUpdatedHParamsFromTable(
p_username varchar(255), p_idParamTable int, p_tablename varchar(255), p_log bool,	p_log_timestamp timestamp,	p_log_idUser int)
	RENAME TO spRemoveUpdatedHParamsFromTable_SaveOldVersion;

CREATE /* OR REPLACE */ FUNCTION public.spRemoveUpdatedHParamsFromTable(
	p_username varchar(255),
    p_idParamTable int,
	p_tablename varchar(255), 
	p_log bool,
	p_log_timestamp timestamp = null,
	p_log_idUser int = null	)
	returns void
	as $$
	
	declare p_qry varchar;
    declare p_paramTableName varchar(255);
    declare p_paramName varchar(255);
    declare cur cursor for select PT.TableName, PD.Name from public.ParamDefs PD inner join public.ParamTables PT on PD.idParamTable = PT.idParamTable where PD.idParamTable = p_idParamTable;
    declare changeLogLevel int;

begin
	if p_log then
	
        select coalesce(cast(DBPROPValue as int), 30) into changeLogLevel from DbInfo where upper(DBPROPERTY) = 'CHANGELOG_LEVEL';

		CREATE TEMPORARY TABLE tt_ChangeLogHP(
			dtTimeStamp timestamp NOT NULL,
			idUser int NOT NULL,
			Operation char(1) NOT NULL,
			ObjectUID uuid NULL,
			ChangeType smallint NOT NULL,
			TableName varchar(4000) NULL,
			ColumnName varchar(4000) NULL,
			OldValue varchar NULL,
			NewValue varchar NULL);
 		create index tt_clHP_user on tt_ChangeLogHP(idUser);
		create index tt_clHP_ObjectUID on tt_ChangeLogHP(ObjectUID);


		OPEN cur; 		
		
		FETCH NEXT FROM cur INTO p_paramTableName, p_paramName;	
		WHILE FOUND LOOP
			p_qry := 
		'INSERT INTO tt_ChangeLogHP (dtTimeStamp, idUser, Operation, ObjectUID, ChangeType, TableName, ColumnName, OldValue)
		SELECT $1, $2, ''D'', O.UID, 1, ''' || p_paramTableName || ''', ''' || p_paramName || ''', "' || p_paramName || '"
			FROM ' || p_paramTableName || '_' || p_username || ' P
			INNER JOIN ObjectsToUpdate_' || p_username || ' O ON P.idObject = O.idObject
			WHERE "' || p_paramName || '#DEFINED" = true;';
			  
			execute p_qry using p_log_timestamp, p_log_idUser;
			FETCH NEXT FROM cur INTO p_paramTableName, p_paramName;
		END LOOP;			
		CLOSE cur;		
	end if;
	
	p_qry:='delete from '||p_tablename||' where idObject in (select idObject FROM ObjectsToUpdate_'||p_username||')';	
	execute p_qry;

	p_qry:='delete from '||p_tablename||' TT USING '||p_tablename||' T inner join '||p_tablename||'_'||p_username||' U on T.idObject=U.idObject 
		where TT.idObject=T.idObject';	
	execute p_qry;


	if p_log then	
		insert into public.ChangeLog (dtTimeStamp, idUser, Operation, ObjectUID, ChangeType, TableName, ColumnName, OldValue, NewValue) 
			select dtTimeStamp, idUser, Operation, ObjectUID, ChangeType, TableName, ColumnName, OldValue, NewValue from tt_ChangeLogHP
			    where ChangeType = 0 OR changeLogLevel >= 20;
					
		drop table tt_ChangeLogHP;
	end if;
end;
$$ language plpgsql; 

alter function public.spRemoveUpdatedHParamsFromTable owner to cadlib_user_role;
