--
-- Name: plan_section_insert(); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE or replace FUNCTION plan_section_insert() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
Declare	
		fieldname varchar;
		primarykey uuid;
		keytype varchar;
		baseentityid int;
		sqltext varchar;
BEGIN

	select e.BaseEntity, New.SectionAlias, ea.EntitiesAttributesId, ea.SystemType
		into baseentityid, fieldname, primarykey, keytype
	from VCLib.T_ENTITIES e
		left join VCLib.T_ENTITIES_ATTRIBUTES ea on ea.EntityID = e.EntityID and ea.IsPrimaryKey = true
	where e.EntityID = New.Entities;

	WHILE (baseentityid is not null) LOOP
		select e.BaseEntity, ea.EntitiesAttributesId, ea.SystemType
			into baseentityid, primarykey, keytype
		from VCLib.T_ENTITIES e
			join VCLib.T_ENTITIES_ATTRIBUTES ea on ea.EntityID = e.EntityID and ea.IsPrimaryKey = true
		where e.EntityID = baseentityid;
	END LOOP;

	sqltext := (
					select string_agg('ALTER TABLE FIES.' || ts.TableName || ' ADD ' || @FieldName || ' ' || (case when @KeyType = 'guid' then 'uuid' when @KeyType = 'string' then 'varchar(255)' else @KeyType end) || ' null;', '')
					from (
								  select 'T_PLAN_ITEM' as TableName
							union select 'T_PLAN_ITEM_LIMITS'
							union select 'T_SECTION_DEPEND_SETTINGS'
							union select 'T_SMO_COEF_MONTH_VALUES'
							union select 'T_FACT_ITEM'
							union select 'T_MEETING_RESOLVE'
							union select 'T_SPLIT_SETTINGS'
							union select 'T_FACT_ITEM_SUM'
							union select 'T_EXTERNAL_PLAN_SUM'
							union select 'T_COEFFICENT_STATINFO'
							union select 'T_BASE_TARIFF'
							union select 'T_MO_PARAMETER_ITEM'
							union select 'T_TP_TYPE'
							union select 'T_TP_FIN_CLOSE_ITEM'
							union select 'T_PLAN_FACT_STATISTICS'
						 ) ts
			   );
	EXECUTE sqltext;

	Return New;

END;
$$;



--
-- Name: plan_section_insert; Type: TRIGGER; Schema: public; Owner: sa
--
DO 
$$ 
BEGIN 
IF EXISTS(SELECT * 
                     FROM information_schema.triggers 
                     WHERE event_object_table = lower('T_PLAN_SECTION') 
                     AND trigger_name = lower('plan_section_insert')
                     ) 
                    THEN 
                    DROP TRIGGER plan_section_insert ON FIES.T_PLAN_SECTION;
    END IF ; 

END; 
$$ ;

CREATE TRIGGER plan_section_insert AFTER INSERT ON FIES.T_PLAN_SECTION FOR EACH ROW EXECUTE PROCEDURE plan_section_insert();
