--atualiza pai e filho chamada via trigger
CREATE OR REPLACE FUNCTION gtcfncupdatematerialson() 
RETURNS trigger as $BODY$
    DECLARE

DECLARE
 
         row_data    RECORD;
         row_data1   RECORD;
 
         fatherCategory  char(2);
         fatherLevel     char(1);
 
         loopX       int;
         tag         char(5);
         fieldS      char(3);
         subFieldS   char(1);
 
         currentControlNumber    	     int;
         currentFieldId          	     char(3);
         currentSubFieldId       	     char(1);
         currentContent          	     text;
         currentSearchContent    	     text;
	 currentSearchContentForSearchModule text;
         currentLine             	     int;
 
     BEGIN
 
         IF (TG_OP != 'DELETE') THEN
 
             currentControlNumber      	            := NEW.controlnumber;
             currentFieldId        		    := NEW.fieldid;
             currentSubFieldId     		    := NEW.subfieldid;
             currentContent        		    := NEW.content;
             currentSearchContent                   := NEW.searchcontent;
             currentSearchContentForSearchModule    := NEW.searchcontentforsearchmodule;
             currentLine             		    := NEW.line;
 
         ELSE
 
             currentControlNumber    		    := OLD.controlnumber;
             currentFieldId         		    := OLD.fieldid;
             currentSubFieldId       	    	    := OLD.subfieldid;
             currentContent          		    := OLD.content;
             currentSearchContent   		    := OLD.searchcontent;
             currentSearchContentForSearchModule    := OLD.searchcontentforsearchmodule;
             currentLine             		    := OLD.line;
 
         END IF;
 
         /**
         * BUSCA CATEGORIA E LEVEL DO PAI
         */
         FOR row_data IN (SELECT  category, level FROM  gtcMaterialControl WHERE  controlnumber = currentControlNumber)
         LOOP
             fatherCategory  := row_data.category;
             fatherLevel     := row_data.level;
         END LOOP;
 
         FOR row_data1 IN
         (
             SELECT  LK.tag, LK.tagson, MC.controlnumber
             FROM  gtcmaterialcontrol MC
         INNER JOIN  gtclinkoffieldsbetweenspreadsheets LK
                 ON  (MC.category = LK.categoryson AND MC.level = LK.levelson )
             WHERE  LK.category         = fatherCategory
             AND  LK.level            = fatherLevel
             AND  LK.tag      like    ('%' || currentFieldId || '.' || currentSubFieldId || '%')
             AND  MC.controlnumberfather = currentControlNumber
             AND  LK.type = '2'
         )
         LOOP
 
             IF (strpos(row_data1.tagson, ',') = 0) THEN
                 row_data1.tagson = row_data1.tagson || ',';
             END IF;
 
             loopX := 1;
 
             LOOP
 
                 tag = split_part(row_data1.tagson, ',', loopX);
 
                 IF char_length(tag) = 0 THEN
   EXIT;
                 END IF;
 
                 fieldS      := split_part(tag, '.', 1);
                 subFieldS   := split_part(tag, '.', 2);
 
                 IF (TG_OP = 'DELETE') THEN
 
   DELETE FROM gtcMaterial
   WHERE controlnumber    = row_data1.controlnumber
   AND fieldid          = fieldS
   AND subfieldid       = subFieldS
   AND line             = currentLine;
 
                 ELSIF (TG_OP = 'UPDATE') THEN
 
   UPDATE gtcMaterial
   SET content                      = currentContent,
       searchcontent                = currentSearchContent,
       searchcontentforsearchmodule = currentSearchContentForSearchModule
   WHERE controlnumber              = row_data1.controlnumber
   AND fieldid                      = fieldS
   AND subfieldid                   = subFieldS
   AND line                         = currentLine;
 
                 ELSIF (TG_OP = 'INSERT') THEN
 
   INSERT INTO gtcMaterial
       (content, searchcontent, searchcontentforsearchmodule, controlnumber, fieldid, subfieldid, line)
   VALUES
       (currentContent, currentSearchContent, currentSearchContentForSearchModule, row_data1.controlnumber, fieldS, subFieldS, currentLine);
 
                 END IF;
 
                 loopX := loopX + 1;
 
             END LOOP;
 
         END LOOP;
 
         RETURN NULL;
     END;
$BODY$ language plpgsql;
