DROP PROCEDURE IF EXISTS sp_DeleteNotificationObject;
DROP FUNCTION IF EXISTS sp_DeleteNotificationObject;

-- =============================================
-- Author:      Vitacore (СЃ)
-- Create date: 2019-10-29
-- Description: Удаление оповещений пользователя с истёкшим сроком
-- =============================================
CREATE PROCEDURE sp_DeleteNotificationObject()
As 
$$
declare _sql text;
BEGIN
	CREATE TEMP TABLE tt AS
	select 'delete FROM ' || S.schema_name || '.' || T.table_name || ' t where t.TimeStop ' || '<' || ' current_date;' 
	                AS run 
	FROM        information_schema.tables T
	JOIN        information_schema.schemata S
	ON          T.table_schema = S.schema_name
	where T.table_name in (select MappingTable from VCLib.T_ENTITIES_ATTRIBUTES
	where EntityID in 
	(
		select NotifyEntity from VCLib.T_NOTIFY_MESSAGE_TYPE
		group by NotifyEntity
	)
	group by MappingTable)
	ORDER BY T.table_name;
	
	_sql = (Select string_agg(run, ' ') as delete_statement from tt);
	
	IF (_sql is not null) THEN
		EXECUTE (_sql);
	END IF;
		
	drop table tt;

END;
$$ Language plpgsql;
