-- @(#)Element_mgr_trigger-patch01-undo.ddl	1.7 05/10/13
--
-- ##################################################################
--
-- What is the change: alter existing function to deal with empty values
--     more correctly
--
-- Why change is needed: removing an existing host location for a given
--     host-based device type wasn't working right
--
-- Risks to existing user data: No risk: this is system data, not user data.
--
CREATE OR REPLACE FUNCTION fntr_element_manager () RETURNS TRIGGER AS '
DECLARE
    rec	    RECORD;
    ipPos   INTEGER;
    hostPos INTEGER;
BEGIN
    SELECT INTO rec \*
    FROM element_manager_template AS emt
    WHERE new.model  = emt.model AND
	  new.vendor = emt.vendor;
    IF FOUND
    THEN
        new.element_manager := rec.location_format;
	hostPos := position(''<host>'' in rec.location_format);
	IF (hostPos > 0)
	THEN
            IF (rec.host IS NOT NULL AND length(rec.host) > 0)
            THEN
                new.element_manager := replace(rec.location_format,
                ''<host>'', rec.host);
            ELSE
                new.element_manager := null;
		RETURN NEW;
            END IF;
	END IF;
	ipPos := position(''<ip>'' in rec.location_format);
	IF (ipPos > 0)
	THEN
            IF (new.ip_address IS NOT NULL)
            THEN
                new.element_manager := replace(new.element_manager,
                ''<ip>'', new.ip_address);
            END IF;
	END IF;
    END IF;
    RETURN NEW;
END;
 '
  LANGUAGE 'plpgsql' VOLATILE;
--
--
--
CREATE OR REPLACE FUNCTION fntr_element_manager_template () RETURNS TRIGGER AS '
DECLARE
    rec     RECORD;
    ipPos   INTEGER;
    hostPos INTEGER;
BEGIN
    FOR rec IN
        SELECT \* FROM element
        WHERE new.model  = element.model AND
              new.vendor = element.vendor
    LOOP
        rec.element_manager := new.location_format;
        hostPos := position(''<host>'' in new.location_format);
        IF (hostPos > 0)
        THEN
            IF (new.host IS NOT NULL)
            THEN
                rec.element_manager := replace(rec.element_manager, ''<host>'', new.host);
                UPDATE element SET element_manager = rec.element_manager
                WHERE new.model = element.model
                AND new.vendor = element.vendor;
            ELSE
                rec.element_manager := null;
                UPDATE element SET element_manager = rec.element_manager
                WHERE new.model = element.model
                AND new.vendor = element.vendor;
                RETURN NEW;
            END IF;
        END IF;
        ipPos := position(''<ip>'' in rec.element_manager);
        IF (ipPos > 0)
        THEN
            IF (rec.ip_address IS NOT NULL)
            THEN
                rec.element_manager := replace(rec.element_manager, ''<ip>'', rec.ip_address);
                UPDATE element SET element_manager = rec.element_manager
                WHERE new.model = element.model
                AND new.vendor = element.vendor;
            END IF;
        END IF;
    END LOOP;
    RETURN NEW;
END;
 ' LANGUAGE 'plpgsql' ;
