CREATE OR REPLACE FUNCTION gtcfncupdatematerialfatherreferences()
  RETURNS trigger AS
$BODY$
    DECLARE

DECLARE
     row_data RECORD;
     old_tag varchar;

     BEGIN
         old_tag = '';

        IF OLD.controlnumberfather = NEW.controlnumberfather
        THEN
            RETURN NULL;
        END IF;

         FOR row_data IN
         (
            SELECT controle.controlnumber,
                   relacionamento.tagson, 
                   split_part(relacionamento.tagson, '.', 1) as fieldId,
                   split_part(relacionamento.tagson, '.', 2) as subfieldId,
                   content.content,
                   content.searchcontent,
                   content.searchcontentforsearchmodule,
                   content.line
              FROM gtcmaterialcontrol controle
        INNER JOIN gtclinkoffieldsbetweenspreadsheets relacionamento
                ON (controle.category = relacionamento.categoryson AND controle.level = relacionamento.levelson )
         LEFT JOIN gtcmaterial content
                ON (content.fieldid || '.' || content.subfieldid = relacionamento.tag AND content.controlnumber = controle.controlnumberfather)
             WHERE controle.controlnumber = OLD.controlnumber
               AND relacionamento.type = '2'
          ORDER BY relacionamento.tagson, content.line
         )

         LOOP
             -- Apaga valores da tag. 
             IF row_data.tagson <> old_tag
             THEN
                -- APAGA valores da tag.
                DELETE FROM gtcMaterial
                      WHERE controlnumber = row_data.controlnumber
                        AND fieldid = row_data.fieldId
                        AND subfieldid = row_data.subfieldId;

                 old_tag = row_data.tagson;
              END IF;

             -- Insere valores novos. 
             IF row_data.content IS NOT NULL
             THEN
                 INSERT INTO gtcMaterial (content, searchcontent, searchcontentforsearchmodule, controlnumber, fieldid, subfieldid, line)
                      VALUES (row_data.content, row_data.searchcontent, row_data.searchcontentforsearchmodule, row_data.controlnumber, row_data.fieldId, row_data.subfieldId, row_data.line); 
             END IF; 

         END LOOP;

         RETURN NULL;
     END;
$BODY$
  LANGUAGE plpgsql;