/*
 * @(#)Create_NSM_Functions.ddl	1.58 03/06/06
 *
 * Warning: This file was generated programmatically by mof2ddl.
 *          Do not make changes to this file or they will be lost!
 *
 * Copyright 2001-2003 Sun Microsystems, Inc. All Rights Reserved.
 *
 * This software is the confidential and proprietary information of Sun
 * Microsystems, Inc. ("Confidential Information").  You shall not
 * disclose such Confidential Information and shall use it only in
 * accordance with the terms of the license agreement you entered into
 * with Sun.
 *
 * SUN MAKES NO REPRESENTATIONS OR WARRANTIES ABOUT THE SUITABILITY OF
 * THE SOFTWARE, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED
 * TO THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A
 * PARTICULAR PURPOSE, OR NON-INFRINGEMENT. SUN SHALL NOT BE LIABLE FOR
 * ANY DAMAGES SUFFERED BY LICENSEE AS A RESULT OF USING, MODIFYING OR
 * DISTRIBUTING THIS SOFTWARE OR ITS DERIVATIVES.
 */


/*
   This is a trigger function that is invoked before INSERT/UPDATE
   on every row of the CIM related tables.
   It inserts the last update timestamp and discovery pass number
   during which it was discovered.
   passnumber column is introduced for all the CIM tables from ESM 1.2
   to provide a better performance. 
   Without passnumber column, all the JOINs have to look up the
   DiscoveryTimeStamp table to find out when it is discovered.

   Input param : 
   Output      : OPAQUE
*/
CREATE FUNCTION update_timestamp () RETURNS OPAQUE AS '
DECLARE
    rec RECORD;
BEGIN
    NEW.lastUpdate := timeofday()::timestamp;
    SELECT INTO rec MAX(passnumber) AS passnumber
        FROM DiscoveryTimestamp;
    IF rec.passnumber ISNULL
    THEN NEW.passnumber := 0;
    ELSE NEW.passnumber := rec.passnumber;
    END IF;
    RETURN NEW;
END;
' LANGUAGE 'plpgsql';

/*
   This is a trigger function that is called on every update/insert
   into CIM tables.

   Input param : 
   Output      : OPAQUE
*/
CREATE FUNCTION update_TopoGuiTimestamp () RETURNS OPAQUE AS '
BEGIN
    UPDATE TopoGuiTimestamp set modelLastUpdate = timeofday()::timestamp
        WHERE entry = 1;
    RETURN NEW;
END;
' LANGUAGE 'plpgsql';

/*
   This function is called from Topology Engine to check whether any data
   has been changed since the last update.
   If it has, Topology Enging calls getModelNodes(), PersistenceManager API
   to refresh its cache.

   Input param : 
   Output
         TRUE or FALSE
*/
CREATE FUNCTION isTopologyChanged () RETURNS BOOLEAN AS '
DECLARE
    rec RECORD;
BEGIN
    SELECT INTO rec *
    FROM TopoGuiTimestamp 
    WHERE entry = 1;
    IF NOT FOUND THEN
       RETURN ''t'';
    END IF;

    IF ( rec.topoRendering ISNULL ) OR ( rec.modelLastUpdate ISNULL )
    THEN RETURN ''t'';
    END IF;

    IF rec.topoRendering < rec.modelLastUpdate
    THEN RETURN ''t'';
    ELSE RETURN ''f'';
    END IF;
END;
' LANGUAGE 'plpgsql';

/*
   This function is called after rendering Topology infor
   to Topology engine to update TopoGuiTimestamp table.

   Input param : 
   Output
         TRUE or FALSE
*/
CREATE FUNCTION topologyRendered () RETURNS BOOLEAN AS '
BEGIN
    UPDATE TopoGuiTimestamp set topoRendering = timeofday()::timestamp
        WHERE entry = 1;
    RETURN ''t'';
END;
' LANGUAGE 'plpgsql';

/*
   Given two protocolendpoints, it checks for the existence of
   CIM_ActiveConnection.

   Input param : 
         pep1key : Objectpath of CIM_ProtocolEndPoint
         pep2key : Objectpath of CIM_ProtocolEndPoint
   Output
         TRUE or FALSE
*/
CREATE FUNCTION isActiveConnection (VARCHAR, VARCHAR) RETURNS BOOLEAN AS '
DECLARE
    rec RECORD;
    pep1key ALIAS FOR $1;
    pep2key ALIAS FOR $1;

BEGIN
        SELECT INTO rec *
        FROM CIM_ActiveConnection 
        WHERE antecedent = pep1key OR
              dependent  = pep2key OR
              antecedent = pep2key OR
              dependent  = pep1key;
        IF NOT FOUND 
        THEN
            RETURN ''f'';
        ELSE
            RETURN ''t'';
        END IF;
END;
' LANGUAGE 'plpgsql';

/*
   Given 2 FCPorts, it verifies attachedports.
   If it finds any invalid links(c:s:c) between those two ports,
   then delete them.

   Input param : 
         portkey         : Objectpath of CIM_FCPort, One side of Attached Port
         attachedportkey : Objectpath of CIM_FCPort, Other side of Attached Port
   Output
         TRUE or FALSE
*/
CREATE FUNCTION verifyAttachedport(VARCHAR, VARCHAR) RETURNS BOOLEAN AS '
DECLARE
    portkey         ALIAS FOR $1;
    attachedportkey ALIAS FOR $2;
    rec1            RECORD;
    rec2            RECORD;
    rec3            RECORD;
    rec4            RECORD;
    port1Stale      BOOLEAN;
    port2Stale      BOOLEAN;
BEGIN
    SELECT INTO rec1
           removable      AS stale
    FROM   Port
    WHERE  key = portkey;
    IF NOT FOUND
    THEN
        RETURN ''f'';
    END IF;
    port1Stale := rec1.stale;

    SELECT INTO rec1
           removable      AS stale
    FROM   Port
    WHERE  key = attachedportkey;
    IF NOT FOUND
    THEN
        RETURN ''f'';
    END IF;
    port2Stale := rec1.stale;

    IF (port1Stale OR port2Stale)
    THEN 
         RETURN ''t'';
    END IF;
    --  For each ProtoEndPoint for a given FCPort
    FOR rec1 IN 
        SELECT pep.objectpath AS pepkey
        FROM   CIM_ProtocolEndPoint pep,
               CIM_DeviceSAPImplementation dsi
        WHERE  dsi.antecedent = portkey AND
               dsi.dependent  = pep.objectpath
    LOOP
        FOR rec2 IN
            SELECT pep.objectpath AS pepkey
            FROM   CIM_ProtocolEndPoint pep,
                   CIM_DeviceSAPImplementation dsi
            WHERE  dsi.antecedent = attachedportkey AND
                   dsi.dependent  = pep.objectpath
        LOOP
            -- DELETE CIM_ActiveConnection
            DELETE FROM CIM_ActiveConnection
            WHERE  (antecedent = rec1.pepkey AND
                    dependent  = rec2.pepkey) OR
                   (antecedent = rec2.pepkey AND
                    dependent  = rec1.pepkey);
            -- DELETE CIM_InLogicalNetwork
            FOR rec3 IN
                SELECT iln1.objectpath      AS iln1key,
                       iln2.objectpath      AS iln2key
                FROM  CIM_InLogicalNetwork  iln1,
                      CIM_LogicalNetwork    ln,
                      CIM_InLogicalNetwork  iln2
                WHERE iln1.member = rec1.pepkey       AND
                      iln1.collection = ln.objectpath AND
                      ln.objectpath = iln2.collection AND
                      iln2.member = rec2.pepkey
            LOOP
                -- Find Port type
                --  Check whether it is an T3 Port.
                   SELECT INTO rec4 *
                   FROM   NXPort    nxp
                   WHERE  nxp.key = portkey AND
                          nxp.hbakey ISNULL;
                   IF FOUND 
                   THEN
                   --  It is an T3 Port, DELETE rec3.iln1key
                       DELETE FROM CIM_InLogicalNetwork
                       WHERE  objectpath = rec3.iln1key;
                   ELSE
                   --  It is an HBA Port, DELETE rec3.iln2key
                       DELETE FROM CIM_InLogicalNetwork
                       WHERE  objectpath = rec3.iln2key;
                   END IF;
            END LOOP; 
        END LOOP;
    END LOOP;     
    DELETE FROM Attachedport
    WHERE  key = portkey||attachedportkey OR
           key = attachedportkey||portkey;
    RETURN ''f'';
END;
' LANGUAGE 'plpgsql';

/*
   Checks whether the data is discovered in the previous discovery
   Returns true if it is stale, false otw

   Input param : 
         inTimestamp  : any TIMESTAMP value
   Output
         TRUE or FALSE
*/
CREATE FUNCTION isStale(TIMESTAMP) RETURNS BOOLEAN AS '
DECLARE
    inTimestamp ALIAS FOR $1;
    endTimestamp TIMESTAMP DEFAULT ''1999-01-08 04:05:06 -8:00'';
    rec RECORD;
BEGIN
    SELECT INTO rec MAX(begintimestamp) AS maxbegin 
    FROM discoverytimestamp 
    WHERE status = ''COMPLETED'';
    IF NOT FOUND THEN
       RETURN ''f'';
    END IF;
    IF inTimestamp < rec.maxbegin
    THEN RETURN ''t'';
    ELSE RETURN ''f'';
    END IF;
END;
' LANGUAGE 'plpgsql';

/*
   Checks whether the Fabric is valid or not.
   Return TRUE when a fabric has at lease one switch and
   the following condition is satisfied.
   When there is only one switch and the Fabric is stale,
   then check whether the switch also belongs to other not staled Fabric.
   if it does return FALSE, if not return TRUE.
   Here is the algorithm to implement the above logic by just
   looking at the data value.
   Abbreviations:
     cimad   : CIM_AdminDomain
     cimcmse : CIM_CollectedMSEs
     cimcs   : CIM_ComputerSystem
     c       : lastUpdate timespamp is current
     s       : lastUpdate timespamp is stale
     cimad*s : the instance of CIM_AdminDomain is stale
     cimad*c : the instance of CIM_AdminDomain is current
   There are 5 cases:
   case1) Currect Fabric without any staled or current switch?
          This will never happen because the join will fail
   case2) A Fabric has just one switch and the switch is offlined?
          cimad*s, cimcmse*s, cimcs*s ==> Return TRUE
          The switch should be removed from GUI, and cause the Fabric
          to be removed.
   case3) A fabric had 2 switches and one the switches is offlined.
          cimad*c, cimcmse*c, cimcs*c for the current switch AND
          cimad*c, cimcmse*s, cimcs*s for the offlined switch
          Both case return TRUE
   case4) A Fabric, F1, had 2 (cascaded) switches, S1 and S2, and
          one of the switch, say S2, is moved to the new Fabric, F2.
          cimad*c, cimcmse*c, cimcs*c for the Fabric F1
          cimad*c, cimcmse*c, cimcs*c for the Fabric F2
   case5) A Fabric, F1, had only one switch, S1, and the S1 is moved
          to the other Fabric, F2.
          cimad*s, cimcmse*s, cimcs*c for the Fabric F1
          cimad*c, cimcmse*c, cimcs*c for the Fabric F2
   Here is the table of the return value based on the staleness.
     cimad    cimcmse      cimcs         Return     case#
       s         s           s            TRUE        2
       c         c           c            TRUE        3
       c         s           s            TRUE        3
       c         c           c            TRUE        4 for F1
       c         c           c            TRUE        4 for F2
       c         c           c            TRUE        5 for F2
       s         s           c            FALSE       5 for F1
   The above are the only possible combinations.
   Then, the implementation will return FALSE for s,s,c states,
   and return TRUE for all others.

   Input param : 
         inValue : Objectpath of CIM_AdminDomain for Fabric
   Output
         TRUE or FALSE
*/
CREATE FUNCTION isValidFabric(VARCHAR) RETURNS BOOLEAN AS '
DECLARE
    inValue   ALIAS FOR $1;
    rec             RECORD;
    rec2            RECORD;
BEGIN
    FOR rec IN 
        SELECT 
             cimad.objectpath             AS cimadobjectpath,
             cimcs.objectpath             AS cimcsobjectpath,
             cimad.lastUpdate             AS cimadtime,
             cimcmse.lastUpdate           AS cimcmsetime,
             cimcs.lastUpdate             AS cimcstime
        FROM   
             CIM_AdminDomain                       AS cimad,
             CIM_CollectedMSEs                     AS cimcmse,
             CIM_ComputerSystem                    AS cimcs
        WHERE 
             inValue = cimad.objectpath            AND
             cimcmse.collection = cimad.objectpath AND
             cimcmse.member  = cimcs.objectpath    AND
             cimcs.Dedicated *= 5             
             ORDER BY (cimad.objectpath) 
    LOOP
       IF isStale(rec.cimadtime) AND isStale(rec.cimcmsetime)
             AND  NOT isStale(rec.cimcstime)
       THEN 
       ELSE 
            IF isStale(rec.cimadtime) AND isStale(rec.cimcmsetime)
               AND isStale(rec.cimcstime)
            THEN
               -- Select all the instances of  CIM_CollectedMSEs
               -- where member = cimcsobjectpath and rec.cimcmsetime
               -- not max.
               -- then return false.
               SELECT INTO rec2 *
               FROM   CIM_CollectedMSEs  AS cimcmse 
               WHERE  cimcmse.member = rec.cimcsobjectpath AND
                      rec.cimcmsetime = 
                      (SELECT MAX (lastupdate)
                       FROM   CIM_CollectedMSEs  AS cimcmse2
                       WHERE cimcmse2.member = rec.cimcsobjectpath) ;
               IF NOT FOUND THEN
               ELSE
                  RETURN ''t'';
               END IF;
            ELSE
               RETURN ''t'';
            END IF;

       END IF;

    END LOOP;     
    RETURN ''f'';
END;
' LANGUAGE 'plpgsql';

/*
   begin_discovery SQL function
   increment passnumber, insert current timestamp into begintimestamp
   returns new passnumber

   Input param : 
   Output
         The latest discovery pass number.
*/
CREATE FUNCTION begin_discovery() RETURNS INTEGER AS '
DECLARE
    oldPassNumber INTEGER;
    newPassNumber INTEGER;
    rec RECORD;
BEGIN
    SELECT INTO rec MAX(passnumber) AS passnumber
        FROM DiscoveryTimestamp;
    oldPassNumber := rec.passnumber;
    IF oldPassNumber ISNULL 
    THEN newPassNumber := 0;
    ELSE newPassNumber := oldPassNumber + 1;
    END IF;
    INSERT INTO DiscoveryTimestamp (passnumber, beginTimestamp) 
        VALUES (newPassNumber, timeofday()::timestamp);
    RETURN newPassNumber;
END;
' LANGUAGE 'plpgsql';

/*
   end_discovery SQL function
   $1 is passnumber
   $2 can be {'UNKNOWN', 'COMPLETED', 'ABORTED', 'FAILED'}
   It'll be safe to use enumeration for this
   Updates endtimestamp as timeofday()::timestamp

   Input param : 
         inPassNumber  : Discovery pass number
         inDescription : Discovery description
   Output
         The latest discovery pass number.
*/
CREATE FUNCTION end_discovery (INTEGER, VARCHAR) RETURNS INTEGER AS '
DECLARE
    inPassNumber  ALIAS FOR $1;
    inDescription ALIAS FOR $2;
    rec   RECORD;
BEGIN
    RAISE NOTICE ''Generic_Storage_Insert '';
    PERFORM Generic_Storage_Insert();
    UPDATE DiscoveryTimestamp set endTimestamp = timeofday()::timestamp
        WHERE passnumber = inPassNumber;
    UPDATE DiscoveryTimestamp set status = inDescription
        WHERE passnumber = inPassNumber;
    DELETE FROM DiscoveryTimestamp WHERE passnumber < inPassNumber - 10;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

/*
   end_discovery SQL function
   $1 is passnumber
   initialize status as UNKNOWN
   It'll be safe to use enumeration for this
   Updates endtimestamp as timeofday()::timestamp

   Input param : 
         inPassNumber : Discovery pass number
   Output
         The latest discovery pass number.
*/
CREATE FUNCTION end_discovery (INTEGER) RETURNS INTEGER AS '
DECLARE
    inPassNumber ALIAS FOR $1;
    rec   RECORD;
BEGIN
    RAISE NOTICE ''Generic_Storage_Insert '';
    PERFORM Generic_Storage_Insert();
    UPDATE DiscoveryTimestamp set endTimestamp = timeofday()::timestamp
    WHERE passnumber = inPassNumber;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

/*
   Removes HBA objects in GUIView
   Removes all directed associated associations  and objects that
   belong to this HBA.

   Input param : 
         inObjectpath : Objectpath of CIM_ScsiController
   Output
         1
*/
CREATE FUNCTION HBA_remove (VARCHAR) RETURNS INTEGER AS '
DECLARE
    inObjectpath ALIAS FOR $1;
    NXPortKeys     RECORD;
BEGIN
--  Delete NXPorts
    FOR NXPortKeys IN 
        SELECT cimfcp.objectpath AS portkey,
               cimsc.passNumber  AS passnumber
        FROM CIM_DeviceIdentity   cimdi,
             CIM_FCPort           cimfcp,
             CIM_ScsiController   cimsc
        WHERE 
             cimsc.objectpath = inObjectpath AND
            (inObjectpath = cimdi.systemelement   AND
             cimdi.sameelement = cimfcp.objectpath)   OR
            (inObjectpath = cimdi.sameelement   AND
             cimdi.systemelement = cimfcp.objectpath)
    LOOP
        PERFORM Port_remove (NXPortKeys.portkey);
        DELETE FROM NXPort WHERE key = NXPortKeys.portkey;
    END LOOP;
    DELETE FROM CIM_DeviceIdentity
        WHERE (sameelement   = inObjectpath OR
               systemelement = inObjectpath);
    DELETE FROM CIM_scsicontroller
        WHERE objectpath = inObjectpath;
--  Delete a HBA
    DELETE FROM HBA
        WHERE key = inObjectpath;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

/* 
   Removes Host objects in GUIView
   Removes all directed associated associations and objects that
   belong to this storagehost.

   Input param : 
         inObjectpath : Objectpath of CIM_UnitaryComputerSystem
   Output
         1
*/
CREATE FUNCTION StorageHost_remove (VARCHAR) RETURNS INTEGER AS '
DECLARE
    inObjectpath ALIAS FOR $1;
    hbaKeys       RECORD;
BEGIN
    FOR hbakeys IN 
        SELECT 
              cimsc.objectpath  AS hbakey
        FROM 
              CIM_SystemDevice   cimsd,
              CIM_FCPort         cimfcp,
              CIM_DeviceIdentity cimdi,
              CIM_ScsiController cimsc,
              CIM_UnitaryComputerSystem cimucs
        WHERE 
              inObjectpath = cimucs.objectpath AND
              inObjectpath = cimsd.groupcomponent      AND
              cimsd.partcomponent = cimfcp.objectpath  AND
             (cimsc.objectpath = cimdi.sameelement OR
              cimsc.objectpath = cimdi.systemelement)  AND
             (cimdi.systemelement = cimfcp.objectpath OR 
              cimdi.sameelement = cimfcp.objectpath)
    LOOP
        PERFORM HBA_remove (hbakeys.hbakey);
    END LOOP;     
    DELETE FROM CIM_RunningOS
        WHERE dependent  = inObjectpath;
    DELETE FROM CIM_ParticipatingCS
        WHERE antecedent = inObjectpath;
    DELETE FROM cim_unitarycomputersystem
        WHERE objectpath = inObjectpath;
--  Delete StorageHost
    DELETE FROM StorageHost
        WHERE key = inObjectpath;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

/*
   Removes StorageSubSystem objects in GUIView
   Removes all directed associated associations  and objects that
   belong to this StorageSubSystem.

   Input param : 
         inObjectpath : Objectpath of CIM_UnitaryComputerSystem
   Output
         1
*/
CREATE FUNCTION StorageSubSystem_remove (VARCHAR) RETURNS INTEGER AS '
DECLARE
    inObjectpath ALIAS FOR $1;
    StoragePortKeys     RECORD;
    VolumeKeys          RECORD;
BEGIN
-- Delete Volume
    FOR VolumeKeys IN 
        SELECT cimsv.objectpath AS volkey
        FROM 
           CIM_StorageVolume                     AS cimsv,
           CIM_ControlledBy                      AS cimcb,
           CIM_ScsiController                    AS cimsc,
           CIM_DeviceIdentity                    AS cimdi,
           CIM_FCPort                            AS cimfcp,
           CIM_SystemDevice                      AS cimsd
        WHERE
           cimcb.antecedent = cimsc.objectpath             AND
           cimcb.dependent  = cimsv.objectpath             AND
           cimcb.objectpath NOT LIKE ''%SCSIInterface%''   AND
           ((cimdi.SystemElement = cimfcp.objectpath AND
             cimdi.SameElement = cimsc.objectpath)     OR
            (cimdi.SameElement = cimfcp.objectpath   AND
             cimdi.SystemElement = cimsc.objectpath))      AND
           cimsd.PartComponent = cimfcp.objectpath         AND
           cimsd.GroupComponent = inObjectpath
    LOOP
        PERFORM Volume_remove (VolumeKeys.volkey);
        DELETE FROM Volume WHERE key = VolumeKeys.volkey;
    END LOOP;     

-- Delete StoragePorts
    FOR StoragePortKeys IN 
        SELECT cimfcp.objectpath AS portkey
        FROM CIM_SystemDevice   cimsd,
             CIM_FCPort         cimfcp
        WHERE 
             inObjectpath = cimsd.groupcomponent      AND
             cimsd.partcomponent = cimfcp.objectpath
    LOOP
        PERFORM Port_remove (StoragePortKeys.portkey);
        DELETE FROM NXPort WHERE key = StoragePortKeys.portkey;
    END LOOP;     

--  Delete StorageSubSystem
    DELETE FROM CIM_SystemDevice 
        WHERE groupcomponent = inObjectpath;
    DELETE FROM CIM_UnitaryComputerSystem
        WHERE objectpath = inObjectpath;
    DELETE FROM CIM_PhysicalPackage
        WHERE objectpath = inObjectpath;
    DELETE FROM StorageSubSystem
        WHERE key = inObjectpath;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

/*
   Removes CIM_FCPort and related associations

   Input param : 
         inObjectpath : Objectpath of CIM_FCPort
   Output
         1
*/
CREATE FUNCTION Port_remove (VARCHAR) RETURNS INTEGER AS '
DECLARE
    inObjectpath ALIAS FOR $1;
BEGIN
    PERFORM AttachedPort_remove(inObjectpath);
--  Delete Port
    DELETE FROM CIM_FCPort
        WHERE objectpath = inObjectpath;
    DELETE FROM CIM_SystemDevice
        WHERE partcomponent = inObjectpath;
    DELETE FROM CIM_DeviceIdentity
        WHERE systemelement = inObjectpath OR
              sameelement = inObjectpath;
    DELETE FROM CIM_Realizes
        WHERE dependent = inObjectpath;
    DELETE FROM CIM_DeviceSAPImplementation
        WHERE antecedent = inObjectpath;
    DELETE FROM CIM_MemberOfCollection
        WHERE Member = inObjectpath;
    DELETE FROM CIM_DeviceSoftware
        WHERE dependent = inObjectpath;

    RETURN 1;
END;
' LANGUAGE 'plpgsql';

/*
   Removes CIM_FCPort and related associations

   Input param : 
         inObjectpath : Objectpath of CIM_StorageVolume
   Output
         1
*/
CREATE FUNCTION Volume_remove (VARCHAR) RETURNS INTEGER AS '
DECLARE
    inObjectpath ALIAS FOR $1;
BEGIN
--  Delete volume
    DELETE FROM CIM_StorageVolume
        WHERE objectpath = inObjectpath;
    DELETE FROM CIM_ControlledBy
        WHERE dependent = inObjectpath;
    DELETE FROM CIM_BasedOn
        WHERE antecedent = inObjectpath;
    DELETE FROM CIM_SCSIInterface
        WHERE dependent = inObjectpath;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

/*
   Removes Switch objects in GUIView
   Removes all directed associated associations  and objects that
   belong to this Switch.

   Input param : 
         inObjectpath : Objectpath of CIM_UnitaryComputerSystem
   Output
         1
*/
CREATE FUNCTION Switch_remove (VARCHAR) RETURNS INTEGER AS '
DECLARE
    inObjectpath ALIAS FOR $1;
    SwitchPortKeys     RECORD;
    FabricKeys     RECORD;
BEGIN
-- removes Fabric whose sole constituent is the switch being deleted
    FOR FabricKeys IN 
        SELECT sw.FabricKey      AS fkey
        FROM   Switch            sw
        WHERE 
               inObjectpath = sw.key
    LOOP
        PERFORM Fabric_remove (FabricKeys.fkey);
    END LOOP;     

-- Delete SwitchPorts
    FOR SwitchPortKeys IN 
        SELECT cimfcp.objectpath AS portkey
        FROM CIM_SystemDevice   cimsd,
             CIM_FCPort         cimfcp
        WHERE 
             inObjectpath = cimsd.groupcomponent      AND
             cimsd.partcomponent = cimfcp.objectpath
    LOOP
        PERFORM Port_remove (SwitchPortKeys.portkey);
        DELETE FROM SwitchPort WHERE key = SwitchPortKeys.portkey;
    END LOOP;     

--  Delete Switch
    DELETE FROM CIM_ComputerSystemPackage
        WHERE dependent = inObjectpath;
    DELETE FROM CIM_ComputerSystem
        WHERE objectpath = inObjectpath;
    DELETE FROM CIM_CollectedMSEs
        WHERE member = inObjectpath;
    DELETE FROM CIM_InstalledSoftwareElement
        WHERE system = inObjectpath;
    DELETE FROM Switch
        WHERE key = inObjectpath;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

/*
   Removes attached port and related associations

   Input param : 
         inObjectpath : Objectpath of CIM_FCPort || CIM_FCPort
   Output
         1
*/
CREATE FUNCTION AttachedPort_remove (VARCHAR) RETURNS INTEGER AS '
DECLARE
    inObjectpath ALIAS FOR $1;
BEGIN
    DELETE FROM AttachedPort
	WHERE portkey = inObjectpath OR
	      attachedportkey = inObjectpath;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

/*
   Removes Fabric and related associations

   Input param : 
         inObjectpath : Objectpath of CIM_AdminDomain
   Output
         1
*/
CREATE FUNCTION Fabric_remove (VARCHAR) RETURNS INTEGER AS '
DECLARE
    inObjectpath ALIAS FOR $1;
    rec RECORD;
    ZoneKeys     RECORD;
BEGIN
-- Check and see how many switches belong to the fabric.
    SELECT INTO rec count(*)
    FROM   Switch    sw
    WHERE  sw.Fabrickey = inObjectpath;
-- If the count is less than or equal to one 
-- (being deleted by switch_remove), 
-- then the fabric will be deleted.
    IF NOT FOUND THEN
       RETURN NULL;
    END IF;
    IF rec.count < 2 THEN
    -- Remove the Fabric 
    -- First remove the zones that belong to this fabric.
       FOR ZoneKeys IN 
           SELECT zn.key           AS key
           FROM   Zone             zn
           WHERE 
                inObjectpath = zn.FabricKey
       LOOP
           PERFORM Zone_remove (ZoneKeys.key);
       END LOOP;     
    -- Remove Fabric related CIM tables
       DELETE FROM CIM_AdminDomain
	       WHERE objectpath = inObjectpath;
       DELETE FROM CIM_CollectedMSEs
	       WHERE collection = inObjectpath;
    -- Remove a row from Fabric virtual table.
       DELETE FROM Fabric
	       WHERE key = inObjectpath;
    END IF;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

/*
   Removes zone and related associations

   Input param : 
         inObjectpath : Objectpath of CIM_Zone
   Output
         1
*/
CREATE FUNCTION Zone_remove (VARCHAR) RETURNS INTEGER AS '
DECLARE
    inObjectpath ALIAS FOR $1;
BEGIN
-- Fabric may have multiple zones and the zones may span multiple
-- switches. We need to remove zone when a switch is removed, and
-- the zone has only one switch, being removed, as a member.
-- As of now, zone_remove is almost intractable problem and no
-- viable solution is in the horizon.
-- We will leave this function to delete the zones only when a Fabric
-- is being removed.
    DELETE FROM CIM_Zone 
        WHERE objectpath = inObjectpath;
    DELETE FROM Sun_NSM_OOBFAB_ZoneSetMember 
        WHERE member = inObjectpath;
    DELETE FROM Zone 
        WHERE key = inObjectpath;
    
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

/*
   Added soft/hard zone support

   Input param : 
         OtherIdentifyingInfo   : Contains values for IPaddr or domainId
         identifyingdescriptions: Array with description for OtherIdentifyingInfo
         desc                   : will be 'IP' or 'GDID'
   Output
         The value from OtherIdentifyingInfo
*/
CREATE FUNCTION enforcement_lookup (INTEGER) RETURNS VARCHAR AS '
DECLARE
    inValue ALIAS FOR $1;
BEGIN
    IF inValue = 4
    THEN RETURN '' (hard)'';
    ELSE RETURN '''';
    END IF;
END;
' LANGUAGE 'plpgsql';

/*
   Provides the enum function for GBic type by using a gbic_type_lookup
   table

   Input param : 
         inValue : Integer value of GBic type
   Output
         String value that corresponds to the input integer value
*/
CREATE FUNCTION gbictype_lookup (INTEGER) RETURNS VARCHAR AS '
DECLARE
    inValue ALIAS FOR $1;
    rec RECORD;
BEGIN
    SELECT INTO rec gtl.ToEnglish
    FROM
          gbic_type_lookup                      AS gtl
    WHERE
          gtl.IntValue = inValue;
    IF NOT FOUND THEN
       RETURN NULL;
    END IF;
    RETURN rec.ToEnglish;
END;
' LANGUAGE 'plpgsql';

/*
   Inserts zone key field into Port tables

   Input param : 
         tableName : Name of table where zonekey is to be inserted.
         inValue   : value of CIM_FCPort.
   Output
         1
*/
CREATE FUNCTION zonekey (VARCHAR, VARCHAR) RETURNS INTEGER AS '
DECLARE
    tableName ALIAS FOR $1;
    inValue   ALIAS FOR $2;
    updateString VARCHAR;
    rec RECORD;
    first BOOLEAN;
BEGIN

-- Set up the update sql string
    updateString := '' UPDATE '' || tableName ||
                    '' SET   zonekey = '' ||
                    '''''''' || ''{'';
    first := ''t'';
    FOR rec IN 
        SELECT 
               cimz1.objectpath||enforcement_lookup(
               cimz1.ZoneType)                      AS zonekey
        FROM   
               CIM_FCPort                           AS cimfcp,
               CIM_ZoneMember                       AS cimmoc1,
               CIM_Zone                             AS cimz1
        WHERE  
               cimfcp.objectpath = inValue            AND
               cimfcp.objectpath = cimmoc1.member     AND
               cimmoc1.collection = cimz1.objectpath  AND
               cimfcp.passnumber = cimmoc1.passnumber AND
               cimmoc1.passnumber = cimz1.passnumber 
    LOOP
        IF first
        THEN
             first := ''f'';
        ELSE
             updateString := updateString || '', '';
        END IF;
        updateString := updateString || ''"'' || rec.zonekey || ''"'';
    END LOOP;     
    IF NOT FOUND THEN
       RETURN 0;
    END IF;
    updateString := updateString || ''}''|| '''''''' || ''  '' ||
                    ''WHERE key = '' || '''''''' || inValue || '''''''' ||
                    '';'';
    EXECUTE updateString;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

/*
   Given a identifyingdescriptions, returns OtherIdentifyingInfo that is 
   located in the corresponding place.

   Input param : 
         OtherIdentifyingInfo   : Contains values for IPaddr or domainId
         identifyingdescriptions: Array with description for OtherIdentifyingInfo
   Output
         The value from OtherIdentifyingInfo
*/
CREATE FUNCTION OtherIdentifyingInfo_lookup 
    (_VARCHAR, _VARCHAR, VARCHAR) RETURNS VARCHAR AS '
DECLARE
    OtherIdentifyingInfo ALIAS FOR $1;
    identifyingdescriptions ALIAS FOR $2;
    desc ALIAS FOR $3;
    counter INTEGER;
    value VARCHAR = NULL;
BEGIN
    counter := 1;
    LOOP
        IF ( identifyingdescriptions[counter] ISNULL ) 
        THEN
            EXIT;  -- Either the array is null or reached the end, exit loop
        END IF;
        IF ( identifyingdescriptions[counter] = desc)
        THEN
            value := OtherIdentifyingInfo[counter];
            EXIT;  -- FOUND it, exit loop
        END IF;
        counter := counter + 1;
    END LOOP;
    RETURN value;
END;
' LANGUAGE 'plpgsql';

/*
   Given a FCPort, returns gbic type of the port from the following JOIN
   CIM_FCPort * CIM_Realizes * CIM_PhysicalConnector.
   Used by Port, SwitchPort, and NXPort views.

   Input param : 
         inValue   : Objectpath of CIM_FCPort
   Output
         GBIC (Connector) type of the port.
*/
CREATE FUNCTION gbictype (VARCHAR) RETURNS VARCHAR AS '
DECLARE
    inValue ALIAS FOR $1;
    rec  RECORD;
    rec2 RECORD;
BEGIN
     SELECT INTO rec cimpc2.connectortype[3]     AS ctype,
                     cimpc2.othertypedescription AS desc
     FROM
           CIM_FCPort                           AS cimfcp,
           CIM_Realizes                         AS cimr2,
           CIM_PhysicalConnector                AS cimpc2
     WHERE
           cimfcp.objectpath = inValue          AND
           cimfcp.objectpath = cimr2.dependent  AND
           cimr2.antecedent = cimpc2.objectpath AND
           cimfcp.passnumber = cimr2.passnumber AND
           cimr2.passnumber = cimpc2.passnumber;

    IF NOT FOUND THEN
       RETURN NULL;
    END IF;
    IF rec.ctype = 1 THEN
       RETURN rec.desc;
    ELSE
       SELECT INTO rec2 gtl.ToEnglish
       FROM
          gbic_type_lookup                      AS gtl
       WHERE
          gtl.IntValue = rec.ctype;
       IF NOT FOUND THEN
          RETURN NULL;
       END IF;
       RETURN rec2.ToEnglish;
    END IF;
END;
' LANGUAGE 'plpgsql';

/*
   Given a FCPort, returns InstanceID of CIM_LogicalPortGroup.
   CIM_FCPort * CIM_MemberOfCollection * CIM_LogicalPortGroup.
   Used by NXPort view

   Input param : 
         inValue   : Objectpath of CIM_FCPort
   Output
         InstanceID of CIM_LogicalPortGroup
*/
CREATE FUNCTION nodeWWN (VARCHAR) RETURNS VARCHAR AS '
DECLARE
    inValue ALIAS FOR $1;
    rec RECORD;
BEGIN
     SELECT INTO rec cimlpg5.instanceid
     FROM
           CIM_FCPort                           AS cimfcp,
           CIM_MemberOfCollection               AS cimmoc5,
           CIM_LogicalPortGroup                 AS cimlpg5
     WHERE
           cimfcp.objectpath = inValue             AND
           cimfcp.objectpath = cimmoc5.Member      AND
           cimmoc5.Collection = cimlpg5.objectpath AND
           cimfcp.passnumber = cimmoc5.passnumber  AND
           cimmoc5.passnumber = cimlpg5.passnumber;
    IF NOT FOUND THEN
       RETURN NULL;
    END IF;
    RETURN rec.instanceid;
END;
' LANGUAGE 'plpgsql';

/*
   Given a FCPort, returns associated objectpath of CIM_StorageVolume
   ComputerSystemFCPort * ScsiControllerFCPort * CIM_ControlledBy *
   CIM_StorageVolume
   Used by NXPort view

   Input param : 
         inValue   : Objectpath of CIM_FCPort
   Output
         objectpath of CIM_StorageVolume
*/
CREATE FUNCTION LogicalDeviceKey (VARCHAR) RETURNS VARCHAR AS '
DECLARE
    inValue ALIAS FOR $1;
    rec RECORD;
BEGIN
     SELECT INTO rec cimsv.objectpath
     FROM
           ComputerSystemFCPort                  AS csfp,
           ScsiControllerFCPort                  AS scfp,
           CIM_ControlledBy                      AS cimcb,
           CIM_StorageVolume                     AS cimsv
     WHERE 
           csfp.fcpportkey = inValue                 AND
           (csfp.Dedicated *= 3 OR csfp.Dedicated *= 15) AND
           scfp.fcpportkey = csfp.fcpportkey         AND
           csfp.cspassnumber = csfp.sdpassnumber     AND
           csfp.sdpassnumber = csfp.fcppassnumber    AND
           csfp.fcppassnumber  = scfp.scpassnumber   AND
           scfp.scpassnumber  = scfp.dipassnumber    AND
           scfp.dipassnumber  = scfp.fcppassnumber   AND
           scfp.fcppassnumber = cimcb.passnumber     AND
           cimcb.passnumber = cimsv.passnumber       AND
           cimcb.antecedent = scfp.scsicontrollerkey AND
           cimcb.dependent = cimsv.objectpath        AND
           cimcb.objectpath NOT LIKE ''%SCSIInterface%''     
;
    IF NOT FOUND THEN
       RETURN NULL;
    END IF;
    RETURN rec.objectpath;
END;
' LANGUAGE 'plpgsql';

/*
   Given a CIM_UnitaryComputerSystem, returns associated objectpath of 
   CIM_OperatingSystem from the following JOIN:
   CIM_UnitaryComputerSystem * CIM_RunningOS * CIM_OperatingSystem
   Used by StorageHost view

   Input param : 
         inValue   : Objectpath of CIM_UnitaryComputerSystem
   Output
         objectpath of CIM_OperatingSystem
*/
CREATE FUNCTION StorageHost_OS (VARCHAR) RETURNS VARCHAR AS '
DECLARE
    inValue ALIAS FOR $1;
    rec RECORD;
BEGIN
     SELECT INTO rec cimos.name
     FROM
           CIM_UnitaryComputerSystem              AS cimucs,
           CIM_RunningOS                          AS cimros,
           CIM_OperatingSystem                    AS cimos
     WHERE
           inValue = cimucs.objectpath                  AND
           cimucs.objectpath = cimros.dependent         AND
           cimros.antecedent  = cimos.objectpath        AND
           cimucs.passnumber = cimros.passnumber        AND
           cimros.passnumber = cimos.passnumber;
    IF NOT FOUND THEN
       RETURN NULL;
    END IF;
    RETURN rec.name;
END;
' LANGUAGE 'plpgsql';

/*
   Given a CIM_UnitaryComputerSystem, returns associated objectpath of 
   CIM_Cluster from the following JOIN:
   CIM_UnitaryComputerSystem * CIM_ParticipatingCS * CIM_Cluster
   Used by StorageHost view

   Input param : 
         inValue   : Objectpath of CIM_UnitaryComputerSystem
   Output
         objectpath of CIM_Cluster
*/
CREATE FUNCTION ClusterKey (VARCHAR) RETURNS VARCHAR AS '
DECLARE
    inValue ALIAS FOR $1;
    rec RECORD;
BEGIN
     SELECT INTO rec cimc.objectpath
     FROM
           CIM_UnitaryComputerSystem              AS cimucs,
           CIM_ParticipatingCS                    AS cimpcs,
           CIM_Cluster                            AS cimc
     WHERE
           inValue = cimucs.objectpath               AND
           cimucs.objectpath = cimpcs.antecedent     AND
           cimpcs.dependent  = cimc.objectpath       AND
           cimucs.passnumber = cimpcs.passnumber     AND
           cimpcs.passnumber = cimc.passnumber;
    IF NOT FOUND THEN
       RETURN NULL;
    END IF;
    RETURN rec.objectpath;
END;
' LANGUAGE 'plpgsql';

/*
   Given a CIM_UnitaryComputerSystem, returns associated version of 
   CIM_PhysicalMedia from the following JOIN:
   ComputerSystemFCPort * ScsiControllerFCPort * CIM_ControlledBy *
   CIM_StorageVolume * CIM_BasedOn * CIM_StorageExtent *
   CIM_Realizes * CIM_PhysicalMedia
   Used by StorageSubSystem view

   Input param : 
         inValue   : Objectpath of CIM_UnitaryComputerSystem
   Output
         version of CIM_PhysicalMedia
*/
CREATE FUNCTION look_firmWareRev (VARCHAR) RETURNS VARCHAR AS '
DECLARE
    inValue ALIAS FOR $1;
    rec RECORD;
BEGIN
     SELECT INTO rec T2.pmversion                   AS version
     FROM
	   StorageSubSystemTemp2                    AS sst2,
           (SELECT T1.pmversion                     AS pmversion,
                   cimr.dependent                   AS cimrdependent,
                   cimr.passnumber                  AS cimrpassnumber
            FROM CIM_Realizes                       AS cimr,
           (SELECT cimpm.objectpath                 AS pmobjectpath,
                   cimpm.version                    AS pmversion,
                   cimpm.passnumber                 AS pmpassnumber
            FROM CIM_PhysicalMedia                  AS cimpm) T1
            WHERE T1.pmobjectpath = cimr.antecedent AND
                  T1.pmpassnumber = cimr.passnumber ) T2
            WHERE T2.cimrdependent = sst2.seobjectpath AND
                  T2.cimrpassnumber = sst2.passnumber  AND
                  inValue = sst2.ucsobjectpath
;

    IF NOT FOUND THEN
       RETURN NULL;
    END IF;
    RETURN rec.version;
END;
' LANGUAGE 'plpgsql';

/*
   Given a CIM_UnitaryComputerSystem, returns associated manufacturer of 
   CIM_PhysicalMedia from the following JOIN:
   ComputerSystemFCPort * ScsiControllerFCPort * CIM_ControlledBy *
   CIM_StorageVolume * CIM_BasedOn * CIM_StorageExtent *
   CIM_Realizes * CIM_PhysicalMedia
   Used by StorageSubSystem view

   Input param : 
         inValue   : Objectpath of CIM_UnitaryComputerSystem
   Output
         manufacturer of CIM_PhysicalMedia
*/
CREATE FUNCTION look_manufacturer (VARCHAR) RETURNS VARCHAR AS '
DECLARE
    inValue ALIAS FOR $1;
    rec RECORD;
BEGIN
     SELECT INTO rec T2.pmmanufacturer              AS manufacturer
     FROM
	   StorageSubSystemTemp2                    AS sst2,
           (SELECT T1.pmmanufacturer                AS pmmanufacturer,
                   cimr.dependent                   AS cimrdependent,
                   cimr.passnumber                  AS cimrpassnumber
            FROM CIM_Realizes                       AS cimr,
           (SELECT cimpm.objectpath                 AS pmobjectpath,
                   cimpm.manufacturer               AS pmmanufacturer,
                   cimpm.passnumber                 AS pmpassnumber
            FROM CIM_PhysicalMedia                  AS cimpm) T1
            WHERE T1.pmobjectpath = cimr.antecedent AND
                  T1.pmpassnumber = cimr.passnumber ) T2
            WHERE T2.cimrdependent = sst2.seobjectpath AND
                  T2.cimrpassnumber = sst2.passnumber  AND
                  inValue = sst2.ucsobjectpath
;

    IF NOT FOUND THEN
       RETURN NULL;
    END IF;
    RETURN rec.manufacturer;
END;
' LANGUAGE 'plpgsql';

/*
   Given a CIM_UnitaryComputerSystem, returns associated model of 
   CIM_PhysicalMedia from the following JOIN:
   ComputerSystemFCPort * ScsiControllerFCPort * CIM_ControlledBy *
   CIM_StorageVolume * CIM_BasedOn * CIM_StorageExtent *
   CIM_Realizes * CIM_PhysicalMedia
   Used by StorageSubSystem view

   Input param : 
         inValue   : Objectpath of CIM_UnitaryComputerSystem
   Output
         model of CIM_PhysicalMedia
*/
CREATE FUNCTION look_model (VARCHAR) RETURNS VARCHAR AS '
DECLARE
    inValue ALIAS FOR $1;
    rec RECORD;
BEGIN
     SELECT INTO rec T2.pmmodel                     AS model
     FROM
	   StorageSubSystemTemp2                    AS sst2,
           (SELECT T1.pmmodel                       AS pmmodel,
                   cimr.dependent                   AS cimrdependent,
                   cimr.passnumber                  AS cimrpassnumber
            FROM CIM_Realizes                       AS cimr,
           (SELECT cimpm.objectpath                 AS pmobjectpath,
                   cimpm.model                      AS pmmodel,
                   cimpm.passnumber                 AS pmpassnumber
            FROM CIM_PhysicalMedia                  AS cimpm) T1
            WHERE T1.pmobjectpath = cimr.antecedent AND
                  T1.pmpassnumber = cimr.passnumber ) T2
            WHERE T2.cimrdependent = sst2.seobjectpath AND
                  T2.cimrpassnumber = sst2.passnumber  AND
                  inValue = sst2.ucsobjectpath
;
    IF NOT FOUND THEN
       RETURN NULL;
    END IF;
    IF trim(both '' '' from rec.model) = ''T4''
    THEN rec.model := ''SE6120'';
    END IF;
    RETURN rec.model;
END;
' LANGUAGE 'plpgsql';

/*
   Given a CIM_UnitaryComputerSystem, returns associated availability of 
   CIM_MediaAccessDevice from the following JOIN:
   ComputerSystemFCPort * ScsiControllerFCPort * CIM_ControlledBy *
   CIM_StorageVolume * CIM_BasedOn * CIM_StorageExtent *
   CIM_MediaPresent * CIM_MediaAccessDevice
   Used by StorageSubSystem view

   Input param : 
         inValue   : Objectpath of CIM_UnitaryComputerSystem
   Output
         availability of CIM_MediaAccessDevice
*/
CREATE FUNCTION look_deviceState (VARCHAR) RETURNS VARCHAR AS '
DECLARE
    inValue ALIAS FOR $1;
    rec RECORD;
    rec2 RECORD;
BEGIN
     SELECT INTO rec T2.madavailability              AS availability
     FROM
	   StorageSubSystemTemp2                     AS sst2,
           (SELECT T1.madavailability                AS madavailability,
                   cimmp.dependent                   AS mpdependent,
                   cimmp.passnumber                  AS mppassnumber
            FROM  CIM_MediaPresent                   AS cimmp,
           (SELECT cimmad.objectpath                 AS madobjectpath,
                   cimmad.availability               AS madavailability,
                   cimmad.passnumber                 AS madpassnumber
            FROM  CIM_MediaAccessDevice              AS cimmad) T1
            WHERE T1.madobjectpath = cimmp.antecedent AND
                  T1.madpassnumber = cimmp.passnumber ) T2
            WHERE T2.mpdependent = sst2.seobjectpath AND
                  T2.mppassnumber = sst2.passnumber  AND
                  inValue = sst2.ucsobjectpath
;

    IF NOT FOUND THEN
       RETURN NULL;
    END IF;
    SELECT INTO rec2 dsl.ToEnglish
    FROM
          device_state_lookup                      AS dsl
    WHERE
          dsl.IntValue = rec.availability;
    IF NOT FOUND THEN
       RETURN NULL;
    END IF;
    RETURN rec2.ToEnglish;
END;
' LANGUAGE 'plpgsql';


/*
   Given an integer value for port type, returns associated String value 
   for the port type
   Used by NXPort and SwitchPort views

   Input param : 
         inValue   : Integer location of port type
   Output
         String value of port type
*/
CREATE FUNCTION look_porttype (INTEGER) RETURNS VARCHAR AS '
DECLARE
    inValue ALIAS FOR $1;
    rec RECORD;
BEGIN
    SELECT INTO rec ptl.ToEnglish
    FROM
          port_type_lookup                      AS ptl
    WHERE
          ptl.IntValue = inValue;
    IF NOT FOUND THEN
       RETURN NULL;
    END IF;
    RETURN rec.ToEnglish;
END;
' LANGUAGE 'plpgsql';

/*
   Given a Sun_NWS_HBA_ScsiInterface, returns associated objectpath of 
   CIM_UnitaryComputerSystem from the following JOIN
   Sun_NWS_HBA_ScsiInterface * ComputerSystemFCPort * ScsiControllerFCPort
   Used by CompositeOSNode view

   Input param : 
         inValue   : Objectpath of Sun_NWS_HBA_ScsiInterface
   Output
         objectpath of CIM_UnitaryComputerSystem
*/
CREATE FUNCTION StorageHostKey (VARCHAR) RETURNS VARCHAR AS '
DECLARE
    inValue ALIAS FOR $1;
    rec RECORD;
BEGIN
     SELECT INTO rec csfp.cskey AS objectpath
     FROM
           Sun_NWS_HBA_ScsiInterface             AS snhsi,
           ComputerSystemFCPort                  AS csfp,
           ScsiControllerFCPort                  AS scfp
     WHERE
           snhsi.objectpath = inValue                  AND
           snhsi.antecedent = scfp.scsicontrollerkey   AND
           scfp.fcpportkey = csfp.fcpportkey           AND
           csfp.Dedicated ISNULL                       AND
           snhsi.passnumber = scfp.scpassnumber        AND
           csfp.cspassnumber = csfp.sdpassnumber       AND
           csfp.sdpassnumber = csfp.fcppassnumber      AND
           csfp.fcppassnumber  = scfp.scpassnumber     AND
           scfp.scpassnumber  = scfp.dipassnumber      AND
           scfp.dipassnumber  = scfp.fcppassnumber
;
    IF NOT FOUND THEN
       RETURN NULL;
    END IF;
    RETURN rec.objectpath;
END;
' LANGUAGE 'plpgsql';


/*
   Given a CIM_ScsiController, returns associated objectpath of 
   CIM_UnitaryComputerSystem from the following JOIN
   ComputerSystemFCPort * ScsiControllerFCPort
   Used by HBA view
   Getting rid of fcport passnumber check due to the bug in IBFab provider,
   4808216. Later this change should be undone after IBFab fixes the bug.

   Input param : 
         inValue   : Objectpath of CIM_ScsiController
   Output
         objectpath of CIM_UnitaryComputerSystem
*/
CREATE FUNCTION HBAStorageHostKey (VARCHAR) RETURNS VARCHAR AS '
DECLARE
    inValue ALIAS FOR $1;
    rec RECORD;
BEGIN
    SELECT INTO rec csfp.cskey AS objectpath
    FROM   
           ComputerSystemFCPort                  AS csfp,
           ScsiControllerFCPort                  AS scfp
    WHERE 
           inValue = scfp.scsicontrollerkey            AND
           scfp.fcpportkey = csfp.fcpportkey           AND
           csfp.Dedicated ISNULL                       AND
           csfp.cspassnumber = csfp.sdpassnumber       AND
           csfp.sdpassnumber = scfp.scpassnumber       AND
           scfp.scpassnumber  = scfp.dipassnumber  
;
    IF NOT FOUND THEN
       RETURN NULL;
    END IF;
    RETURN rec.objectpath;
END;
' LANGUAGE 'plpgsql';

/*
   Swicth may be related to multiple Fabrics which is not valid.
   Join will return multiple Fabrics, but this function needs
   to pick up the latest one.
   Given a Switch Key, returns associated objectpath of CIM_AdminDomain
   CIM_ComputerSystem * CIM_CollectedMSEs * CIM_AdminDomain
   Used by Switch view

   Input param : 
         inValue   : Objectpath of CIM_ComputerSystem
   Output
         objectpath of CIM_StorageVolume
*/
CREATE FUNCTION SwitchFabricKey (VARCHAR) RETURNS VARCHAR AS '
DECLARE
    inValue ALIAS FOR $1;
    rec RECORD;
BEGIN
    SELECT INTO rec 
           cimad0.objectpath                      AS objectpath,
           cimcmses0.lastUpdate                   AS cimcmses0time
    FROM 
           CIM_ComputerSystem                     AS cimcs0,
           CIM_CollectedMSEs                      AS cimcmses0,
           CIM_AdminDomain                        AS cimad0
    WHERE
           inValue = cimcs0.objectpath              AND
           cimcs0.objectpath = cimcmses0.member     AND
           cimcmses0.collection = cimad0.objectpath
           ORDER BY (cimcmses0.lastUpdate) DESC;
    IF NOT FOUND THEN
       RETURN NULL;
    END IF;
    RETURN rec.objectpath;
END;
' LANGUAGE 'plpgsql';

/*
   Given a Sun_NWS_HBA_ScsiInterface, 
   returns associated objectpath of CIM_StorageVolume from following JOIN
   CIM_ScsiInterface * CIM_StorageVolume * CIM_ControlledBy *
   ComputerSystemFCPort * ScsiControllerFCPort
   Used by DeviceOSNode and AsymmetricOSNode views

   Input param : 
         inValue   : Objectpath of CIM_ScsiInterface
   Output
         objectpath of CIM_StorageVolume
*/
CREATE FUNCTION LogicalDeviceKeyOS (VARCHAR) RETURNS VARCHAR AS '
DECLARE
    inValue ALIAS FOR $1;
    rec RECORD;
BEGIN
     SELECT INTO rec cimsv.objectpath
     FROM
          CIM_ScsiInterface                     AS snhsi2,
          CIM_StorageVolume                     AS cimsv,
          CIM_ControlledBy                      AS cimcb,
          ComputerSystemFCPort                  AS csfp,
          ScsiControllerFCPort                  AS scfp
     WHERE
           snhsi2.objectpath = inValue                   AND
           snhsi2.dependent = cimsv.objectpath           AND
           cimcb.dependent = cimsv.objectpath            AND
           cimcb.antecedent = scfp.scsicontrollerkey     AND
           scfp.fcpportkey = csfp.fcpportkey             AND
           (csfp.Dedicated *= 3 OR csfp.Dedicated *= 15) AND
           snhsi2.passnumber = cimsv.passnumber          AND
           cimsv.passnumber = cimcb.passnumber           AND
           cimcb.passnumber = csfp.cspassnumber          AND
           csfp.cspassnumber = csfp.sdpassnumber         AND
           csfp.sdpassnumber = csfp.fcppassnumber        AND
           csfp.fcppassnumber  = scfp.scpassnumber       AND
           scfp.scpassnumber  = scfp.dipassnumber        AND
           scfp.dipassnumber  = scfp.fcppassnumber
;
    IF NOT FOUND THEN
       RETURN NULL;
    END IF;
    RETURN rec.objectpath;
END;
' LANGUAGE 'plpgsql';


/*
   Given a CIM_ComputerSystem, returns associated version of CIM_SoftwareElement
   CIM_ComputerSystem * CIM_InstalledSoftwareElement * CIM_SoftwareElement
   Used by Switch view

   Input param : 
         inValue   : Objectpath of CIM_ComputerSystem
   Output
         version of CIM_SoftwareElement
*/
CREATE FUNCTION Switch_firmwareRev (VARCHAR) RETURNS VARCHAR AS '
DECLARE
    inValue ALIAS FOR $1;
    rec RECORD;
BEGIN
     SELECT INTO rec cimse2.Version
     FROM
           CIM_ComputerSystem                     AS cimcs,
           CIM_InstalledSoftwareElement           AS cimise2,
           CIM_SoftwareElement                    AS cimse2
     WHERE
           inValue = cimcs.objectpath                AND
           cimcs.objectpath = cimise2.System         AND
           cimise2.Software = cimse2.objectpath      AND
           cimcs.passnumber = cimise2.passnumber     AND
           cimise2.passnumber = cimse2.passnumber;
    IF NOT FOUND THEN
       RETURN NULL;
    END IF;
    RETURN rec.Version;
END;
' LANGUAGE 'plpgsql';

/*
   Given a CIM_ComputerSystem, returns associated vendor of CIM_Product
   from the following JOIN
   CIM_ComputerSystem * CIM_InstalledSoftwareElement * CIM_SoftwareElement
   Used by Switch view

   Input param : 
         inValue   : Objectpath of CIM_ComputerSystem
   Output
         vendor of CIM_Product
*/
CREATE FUNCTION Switch_vendor (VARCHAR) RETURNS VARCHAR AS '
DECLARE
    inValue ALIAS FOR $1;
    rec RECORD;
BEGIN
     SELECT INTO rec cimp3.Vendor
     FROM
           CIM_ComputerSystem                     AS cimcs,
           CIM_ComputerSystemPackage              AS cimcsp3,
           CIM_PhysicalPackage                    AS cimpp3,
           CIM_ProductPhysicalElements            AS cimppe3,      
           CIM_Product                            AS cimp3
     WHERE
           inValue = cimcs.objectpath                   AND
           cimcs.objectpath = cimcsp3.dependent         AND
           cimcsp3.antecedent = cimpp3.objectpath       AND
           cimppe3.Component = cimpp3.objectpath        AND
           cimppe3.Product = cimp3.objectpath           AND
           cimcs.passnumber = cimcsp3.passnumber        AND
           cimcsp3.passnumber = cimpp3.passnumber       AND
           cimpp3.passnumber = cimppe3.passnumber       AND
           cimppe3.passnumber = cimp3.passnumber;
    IF NOT FOUND THEN
       RETURN NULL;
    END IF;
    RETURN rec.Vendor;
END;
' LANGUAGE 'plpgsql';

/*
   Given a CIM_ComputerSystem, returns associated model of CIM_PhysicalPackage
   from the following JOIN
   CIM_ComputerSystem * CIM_ComputerSystemPackage * CIM_PhysicalPackage
   Used by Switch view

   Input param : 
         inValue   : Objectpath of CIM_ComputerSystem
   Output
         model of CIM_PhysicalPackage
*/
CREATE FUNCTION Switch_model (VARCHAR) RETURNS VARCHAR AS '
DECLARE
    inValue ALIAS FOR $1;
    rec RECORD;
BEGIN
     SELECT INTO rec cimpp3.model
     FROM  
           CIM_ComputerSystem                     AS cimcs,
           CIM_ComputerSystemPackage              AS cimcsp3,
           CIM_PhysicalPackage                    AS cimpp3
     WHERE
           inValue = cimcs.objectpath                   AND
           cimcs.objectpath = cimcsp3.dependent         AND
           cimcsp3.antecedent = cimpp3.objectpath       AND
           cimcs.passnumber = cimcsp3.passnumber        AND
           cimcsp3.passnumber = cimpp3.passnumber;
    IF NOT FOUND THEN
       RETURN NULL;
    END IF;
    RETURN rec.model;
END;
' LANGUAGE 'plpgsql';

/*
   Given a CIM_ScsiController, returns associated name of CIM_SoftwareElement
   from the following JOIN
   ScsiControllerFCPort * CIM_DeviceSoftware * CIM_SoftwareElement
   Used by Switch view
   Getting rid of fcport passnumber check due to the bug in IBFab provider,
   4808216. Later this change should be undone after IBFab fixes the bug.

   Input param : 
         inValue   : Objectpath of CIM_ComputerSystem
   Output
         name of CIM_SoftwareElement
*/
CREATE FUNCTION HBA_model (VARCHAR) RETURNS VARCHAR AS '
DECLARE
    inValue ALIAS FOR $1;
    rec RECORD;
BEGIN
     SELECT INTO rec cimse1.name
     FROM
           ScsiControllerFCPort                         AS scfp,
           CIM_DeviceSoftware                           AS cimds1,
           CIM_SoftwareElement                          AS cimse1
     WHERE
           inValue = scfp.scsicontrollerkey             AND
           scfp.fcpportkey = cimds1.dependent           AND
           cimse1.objectpath = cimds1.antecedent        AND
           cimds1.passnumber = scfp.scpassnumber        AND
           scfp.scpassnumber  = scfp.dipassnumber 
;
    IF NOT FOUND THEN
       RETURN NULL;
    END IF;
    RETURN rec.name;
END;
' LANGUAGE 'plpgsql';

/*
   Given a CIM_ScsiController, returns associated version of CIM_SoftwareElement
   from the following JOIN
   ScsiControllerFCPort * CIM_DeviceSoftware * CIM_SoftwareElement
   Used by Switch view
   Getting rid of fcport passnumber check due to the bug in IBFab provider,
   4808216. Later this change should be undone after IBFab fixes the bug.

   Input param : 
         inValue   : Objectpath of CIM_ComputerSystem
   Output
         version of CIM_SoftwareElement
*/
CREATE FUNCTION HBA_firmwareRev (VARCHAR) RETURNS VARCHAR AS '
DECLARE
    inValue ALIAS FOR $1;
    rec RECORD;
BEGIN
     SELECT INTO rec cimse1.version
     FROM
           ScsiControllerFCPort                         AS scfp,
           cim_devicesoftware                           AS cimds1,
           cim_softwareelement                          AS cimse1
     WHERE
           inValue = scfp.scsicontrollerkey             AND
           scfp.fcpportkey = cimds1.dependent           AND
           cimse1.objectpath = cimds1.antecedent        AND
           cimds1.passnumber = scfp.scpassnumber        AND
           scfp.scpassnumber  = scfp.dipassnumber
;
    IF NOT FOUND THEN
       RETURN NULL;
    END IF;
    RETURN rec.version;
END;
' LANGUAGE 'plpgsql';

/*
   Given a CIM_ScsiController, returns associated manufacturer of CIM_SoftwareElement
   from the following JOIN
   ScsiControllerFCPort * CIM_DeviceSoftware * CIM_SoftwareElement
   Used by Switch view
   Getting rid of fcport passnumber check due to the bug in IBFab provider,
   4808216. Later this change should be undone after IBFab fixes the bug.

   Input param : 
         inValue   : Objectpath of CIM_ComputerSystem
   Output
         manufacturer of CIM_SoftwareElement
*/
CREATE FUNCTION HBA_vendor (VARCHAR) RETURNS VARCHAR AS '
DECLARE
    inValue ALIAS FOR $1;
    rec RECORD;
BEGIN
     SELECT INTO rec cimse1.manufacturer
     FROM
           ScsiControllerFCPort                         AS scfp,
           cim_devicesoftware                           AS cimds1,
           cim_softwareelement                          AS cimse1
     WHERE
           inValue = scfp.scsicontrollerkey             AND
           scfp.fcpportkey = cimds1.dependent           AND
           cimse1.objectpath = cimds1.antecedent        AND
           cimds1.passnumber = scfp.scpassnumber        AND
           scfp.scpassnumber  = scfp.dipassnumber
;
    IF NOT FOUND THEN
       RETURN NULL;
    END IF;
    RETURN rec.manufacturer;
END;
' LANGUAGE 'plpgsql';

/* 
   Function for supporting Generic storage devices, and is
   called at the end of discovery.
   Input param : 
   Output :
         1
*/
CREATE FUNCTION Generic_Storage_Insert () RETURNS INTEGER AS '
DECLARE
    ucskey VARCHAR;        -- CIM_UnitaryComputerSystem key
    assokey VARCHAR;       -- association keys
    insertString VARCHAR;  -- contains INSERT statement
    updateString VARCHAR;  -- contains UPDATE statement
    rec RECORD;            -- Outer most record
    rec2 RECORD;           -- Intermediate Record
    rec3 RECORD;           -- Inner most record
BEGIN
--  For each rows in CIM_LogicalPortGroup
--  iterate and insert proper rows in a proper table if needed.
--  Find CIM_FCPort and CIM_StorageVolume that satisfy the following JOIN
--  Join CIM_LogicalPortGroup * CIM_MemberOfCollection * CIM_FCPort *
--       CIM_DeviceIdentity * CIM_ScsiController * CIM_ControlledBy *
--       CIM_StorageVolume
    PERFORM refresh_TempTables();
    FOR rec IN 
        SELECT DISTINCT ON (instanceid, cimsvobjectpath, cimfcpobjectpath)
               cimlpg.instanceID                    AS instanceid,
               T4.cimsvobjectpath                   AS cimsvobjectpath,
               T4.scfpfcpportkey                    AS cimfcpobjectpath
        FROM   
               CIM_LogicalPortGroup                 AS cimlpg,
               (SELECT cimmoc.collection            AS cimmoccollection,
                       cimmoc.passnumber            AS cimmocpassnumber,
                       T3.scfpfcpportkey            AS scfpfcpportkey,
                       T3.cimsvobjectpath           AS cimsvobjectpath
                FROM   CIM_MemberOfCollection       AS cimmoc,
               (SELECT scfp.fcpportkey              AS scfpfcpportkey,
                       scfp.scpassnumber            AS scfppassnumber,
                       T2.cimsvobjectpath           AS cimsvobjectpath
                FROM   ScsiControllerFCPort         AS scfp,
               (SELECT cimcb.antecedent             AS cimcbantecedent,
                       cimcb.passnumber             AS cimcbpassnumber,
                       T1.cimsvobjectpath           AS cimsvobjectpath
                FROM   CIM_ControlledBy             AS cimcb,
               (SELECT cimsv.objectpath             AS cimsvobjectpath,
                       cimsv.passnumber             AS cimsvpassnumber
                FROM   CIM_StorageVolume            AS cimsv) T1
                WHERE  T1.cimsvobjectpath = cimcb.dependent AND
                       T1.cimsvpassnumber = cimcb.passnumber) T2
                WHERE  T2.cimcbantecedent = scfp.scsicontrollerkey AND
                       T2.cimcbpassnumber = scfp.scpassnumber AND
                       scfp.dipassnumber = scfp.fcppassnumber AND
                       scfp.otheridentifyinginfo ISNULL) T3
                WHERE  T3.scfpfcpportkey  = cimmoc.member AND
                       T3.scfppassnumber  = cimmoc.passnumber) T4
        WHERE  T4.cimmoccollection = cimlpg.objectpath AND
               NOT isStale(cimlpg.lastUpdate)          AND
               cimlpg.passnumber = T4.cimmocpassnumber
    LOOP
--  Then find out there exist instances that satisfy the following JOINs.
--  CIM_StorageVolume * CIM_SystemDevice * 
--  CIM_UnitaryComputerSystem * CIM_SystemDevice * CIM_FCPort

        SELECT INTO rec2
               cimsv.objectpath
        FROM
               ComputerSystemFCPort            AS csfp,
               ScsiControllerFCPort            AS scfp,
               CIM_ControlledBy                AS cimcb,
               CIM_StorageVolume               AS cimsv
        WHERE  csfp.fcpportkey = rec.cimfcpobjectpath      AND
               csfp.cspassnumber = csfp.sdpassnumber       AND
               csfp.sdpassnumber = csfp.fcppassnumber      AND
               scfp.fcpportkey = csfp.fcpportkey           AND
               csfp.fcppassnumber  = scfp.scpassnumber     AND
               scfp.scpassnumber  = scfp.dipassnumber      AND
               scfp.dipassnumber  = scfp.fcppassnumber     AND
               cimcb.passnumber = csfp.cspassnumber        AND
               cimcb.dependent = cimsv.objectpath          AND
               cimcb.antecedent = scfp.scsicontrollerkey   AND
               NOT isStale(cimsv.lastUpdate);

        IF NOT FOUND THEN
-- Insert Into CIM_UnitaryComputerSystem
            ucskey := 
                ''CIM_UnitaryComputerSystem:'' || 
                ''CreationClassName=CIM_UnitaryComputerSystem:'' ||
                ''Name='' || rec.instanceid || '':'';
            SELECT INTO rec3
                   cimucs3.objectpath            AS objectpath,
                   cimucs3.Dedicated             AS Dedicated
            FROM   
                   CIM_UnitaryComputerSystem     AS cimucs3
            WHERE
                   cimucs3.objectpath = ucskey;
            IF NOT FOUND 
            THEN
                 insertString := 
                     ''INSERT INTO CIM_UnitaryComputerSystem '' ||
                     ''(objectpath, creationclassname, name, status, '' ||
                     ''Dedicated) '' ||
                     ''VALUES ('' ||
                     '''''''' || ucskey || '''''''' || '','' ||
                     ''''''CIM_UnitaryComputerSystem'''''' || '','' ||
                     '''''''' || rec.instanceid || '''''''' || '',''||
                     ''''''Unknown'''''' || '',''||
                     ''''''{3}'''''' ||
                     '');'';
                 EXECUTE insertString;
            ELSE IF NOT (rec3.Dedicated *= 3) AND NOT (rec3.Dedicated *= 15)
                 THEN
                 -- Set the dedicated field to 3, default.
                 updateString := 
                     ''UPDATE CIM_UnitaryComputerSystem '' ||
                     ''SET Dedicated='' ||
                     ''''''{3}'''''' || '','' ||
                     ''status =''||
                     ''''''Unknown'''''' ||
                     '' WHERE objectpath ='' ||
                     '''''''' || ucskey ||'''''''' || '';'';
                 EXECUTE updateString;
                 ELSE
-- We need to update the timestamp here if it already exists.
                 updateString := 
                     ''UPDATE CIM_UnitaryComputerSystem '' ||
                     ''SET status =''||
                     ''''''Unknown'''''' ||
                     '' WHERE objectpath ='' ||
                     '''''''' || ucskey ||'''''''' || '';'';
                 EXECUTE updateString;
                 END IF;
            END IF;

-- Insert Into CIM_SystemDevice 1.
-- Even if it exists, we need to update it with the same data
-- to have a new lastUpdate
            assokey := 
                ''CIM_SystemDevice:'' || 
                ''GroupComponent='' || ucskey || '':'' ||
                ''PartComponent='' || rec.cimsvobjectpath || '':'';
            SELECT INTO rec3
                   cimsd3.objectpath
            FROM   
                   CIM_SystemDevice     AS cimsd3
            WHERE
                   cimsd3.groupcomponent = ucskey AND
                   cimsd3.partcomponent = rec.cimsvobjectpath;
            IF NOT FOUND THEN
                 insertString := 
                     ''INSERT INTO CIM_SystemDevice '' ||
                     ''(objectpath, groupcomponent, partcomponent) ''||
                     ''VALUES ('' ||
                     '''''''' || assokey || '''''''' || '','' ||
                     '''''''' || ucskey || '''''''' || '','' ||
                     '''''''' || rec.cimsvobjectpath || '''''''' || 
                     '');'';
                 EXECUTE insertString;
            ELSE
                 updateString := 
                     ''UPDATE CIM_SystemDevice '' ||
                     ''SET groupcomponent =''||
                     '''''''' || ucskey || '''''''' || '','' ||
                     ''partcomponent =''||
                     '''''''' || rec.cimsvobjectpath || '''''''' || 
                     '' WHERE groupcomponent ='' ||
                     '''''''' || ucskey ||'''''''' || 
                     '' AND partcomponent ='' ||
                     '''''''' || rec.cimsvobjectpath ||'''''''' ||        
                     '';'';
                 EXECUTE updateString;
            END IF;
-- Insert Into CIM_SystemDevice 2
-- Even if it exists, we need to update it with the same data
-- to have a new lastUpdate
            assokey := 
                ''CIM_SystemDevice:'' || 
                ''GroupComponent='' || ucskey || '':'' ||
                ''PartComponent='' || rec.cimfcpobjectpath || '':'';
            SELECT INTO rec3
                   cimsd3.objectpath
            FROM   
                   CIM_SystemDevice     AS cimsd3
            WHERE
                   cimsd3.groupcomponent = ucskey AND
                   cimsd3.partcomponent = rec.cimfcpobjectpath;
            IF NOT FOUND THEN
                 insertString := 
                     ''INSERT INTO CIM_SystemDevice '' ||
                     ''(objectpath, groupcomponent, partcomponent) ''||
                     ''VALUES ('' ||
                     '''''''' || assokey || '''''''' || '','' ||
                     '''''''' || ucskey || '''''''' || '','' ||
                     '''''''' || rec.cimfcpobjectpath || '''''''' || 
                     '');'';
                 EXECUTE insertString;
            ELSE
                 updateString := 
                     ''UPDATE CIM_SystemDevice '' ||
                     ''SET groupcomponent =''||
                     '''''''' || ucskey || '''''''' || '','' ||
                     ''partcomponent =''||
                     '''''''' || rec.cimfcpobjectpath || '''''''' || 
                     '' WHERE groupcomponent ='' ||
                     '''''''' || ucskey ||'''''''' || 
                     '' AND partcomponent ='' ||
                     '''''''' || rec.cimfcpobjectpath ||'''''''' ||        
                     '';'';
                 EXECUTE updateString;
            END IF;
        END IF; 
    END LOOP;     
    RETURN 1;
END;
' LANGUAGE 'plpgsql';


/*
 * This function sets the ip address forT4
 * given objectpath to CIM_UnitaryComputerSystem
 */
CREATE FUNCTION setT4IPAddress () RETURNS INTEGER AS '
DECLARE
    rec RECORD;
    rec2 RECORD;
    iptmpAddr VARCHAR;
BEGIN
     -- Set the IP address from CIM_UnitaryComputerSystem for
     -- T4
     FOR rec IN
         SELECT
               cimucs.otheridentifyinginfo          AS otheridentifyinginfo,
               cimucs.identifyingdescriptions       AS identifyingdescriptions,
               cimucs.objectpath                    AS key
         FROM
               CIM_UnitaryComputerSystem                    AS cimucs
         WHERE
               (cimucs.dedicated *= 3 OR
                cimucs.dedicated *= 15)    AND
               OtherIdentifyingInfo_lookup(cimucs.OtherIdentifyingInfo,
                   cimucs.identifyingdescriptions, ''IP'') NOTNULL
     LOOP
        SELECT INTO rec2
               storagesubsystemkey
        FROM   Annotation
        WHERE  storagesubsystemkey = rec.key;
        IF NOT FOUND
        THEN
            iptmpAddr := OtherIdentifyingInfo_lookup(
                      rec.OtherIdentifyingInfo,
                      rec.identifyingdescriptions,
                      ''IP'');
            INSERT INTO  Annotation
               (storagesubsystemkey, ipaddr, ipeditable)
               VALUES (rec.key, iptmpAddr, ''f'' );
        END IF;
     END LOOP;
     RETURN 0;
END;
' LANGUAGE 'plpgsql';

/*
 * This function sets the ip address for Porsche/Maserati Boxes
 * given objectpath to CIM_PhysicalPackage
 */
CREATE FUNCTION setIPAddress (VARCHAR) RETURNS INTEGER AS '
DECLARE
    inValue ALIAS FOR $1;
    rec RECORD;
    rec2 RECORD;
    insertString VARCHAR;
BEGIN
     SELECT INTO rec 
           rsap.accessinfo                              AS ipaddr 
     FROM
           CIM_PhysicalPackage                          AS pp,
           CIM_ComputerSystemPackage                    AS csp,
           CIM_ComputerSystem                           AS cs,
           CIM_HostedAccessPoint                        AS hap,
           CIM_RemoteServiceAccessPoint                 AS rsap
     WHERE
           inValue = pp.objectpath  AND
           pp.objectpath = csp.antecedent             AND
           csp.dependent = cs.objectpath  AND
           cs.objectpath = hap.antecedent AND
           hap.dependent = rsap.objectpath AND
           pp.passnumber = csp.passnumber        AND
           csp.passnumber  = cs.passnumber       AND
           cs.passnumber  = hap.passnumber      AND
           hap.passnumber = rsap.passnumber
;
    IF FOUND 
    THEN
       SELECT INTO rec2
              storagesubsystemkey
       FROM   Annotation
       WHERE  storagesubsystemkey = inValue;
       IF NOT FOUND
       THEN
           INSERT INTO  Annotation
              (storagesubsystemkey, ipaddr, ipeditable)
              VALUES (inValue,rec.ipaddr, ''f'' );
           RETURN 1;
       END IF;
    END IF;
    RETURN 0;
END;
' LANGUAGE 'plpgsql';


/*
 * This is for Porsche/Maserati/Indy to support the Black box view 
 * of the whole box.
 * Deletes Front-end switches, vicoms, switchports, attachedports, NxPorts, Zones
 * Creates Cabinet Box (Storage type), NxPorts.
 * Updates Volume, LogicalDevice, NxPort for StorageSubSystemKey
 */

CREATE FUNCTION Porsche_discovery () RETURNS INTEGER AS '
DECLARE
    vicomrec            RECORD;     --record to store vicom
    boxrec		RECORD;     --record to store cabinet info
    rec1		RECORD;     --record for inner LOOP
    rec2		RECORD;     --record for inner LOOP
    onerec		RECORD;     --record containing one row
    tmpFabricKey        VARCHAR;
    tmpString           VARCHAR;    --tmp String for insert and update
BEGIN
    -- Clear the temporary delete table
    DELETE FROM PorscheDeleteTable;
    -- Start deleting the devices inside the Box.
    FOR boxrec IN
        SELECT 
               cimucs.objectpath          AS ssskey,
               cimpp.objectpath           AS cimppkey,
               cimpp.Name                 AS name,
               cimpp.Manufacturer         AS vendor,
               cimpp.Model                AS model,
               cimpp.Version              AS firmwareRev,
               cimpp.Status               AS status,
               cimpp.lastUpdate           AS lastUpdate              
        FROM   
               CIM_UnitaryComputerSystem  AS cimucs,
               CIM_ParticipatingCS        AS cimpcs,
               CIM_Cluster                AS cimc,
               CIM_ComputerSystemPackage  AS cimcsp,
               CIM_PhysicalPackage        AS cimpp
        WHERE
               cimucs.objectpath = cimpcs.antecedent AND
               cimpcs.dependent = cimc.objectpath AND
               (cimucs.dedicated *= 3 OR
                cimucs.dedicated *= 15)  AND
               cimc.objectpath = cimcsp.dependent   AND
               cimcsp.antecedent = cimpp.objectpath AND
               cimc.Dedicated *= 15                 AND
               cimc.passnumber = cimcsp.passnumber  AND
               cimc.passnumber = cimpp.passnumber
    LOOP
        -- Found a Vicom box or StorageSubSystem inside Box.
        -- DELETE StorageSubSystem
        INSERT INTO PorscheDeleteTable 
           (TableName, tablekey)
           VALUES (''StorageSubSystem'', boxrec.ssskey);
        -- Start Inserting/Updating the collapsed assets.
        -- Insert StorageSubSystem
        SELECT INTO onerec * FROM StorageSubSystem WHERE key = boxrec.cimppkey;
        IF NOT FOUND 
        THEN
            IF isStale(boxrec.lastUpdate)
            THEN tmpString := ''Lost Comm'';
            ELSE tmpString := boxrec.status;
            END IF; 

        IF trim(both '' '' from boxrec.model) = ''T4''
        THEN  boxrec.model := ''SE6320'';
        END IF;

        INSERT INTO  StorageSubSystem
           (key, removable, status, name, firmwarerev, vendor, model)
           VALUES (boxrec.cimppkey, isStale(boxrec.lastUpdate), tmpString, 
                   boxrec.name, boxrec.firmwareRev, boxrec.vendor,
                   boxrec.model);
            PERFORM setIPAddress(boxrec.cimppkey);
        END IF;
        -- Update StorageSubSystemKey column
        tmpString :=
            ''UPDATE NxPort '' ||
            ''SET StorageSubSystemKey='' ||
            '''''''' || boxrec.cimppkey || '''''''' || '','' ||
            ''nodewwn =''||
            '''''''' || boxrec.name || '''''''' ||
            '' WHERE StorageSubSystemKey='' ||
            '''''''' || boxrec.ssskey ||'''''''' || '';'';
        EXECUTE tmpString;
        tmpString :=
            ''UPDATE LogicalDevice '' ||
            ''SET StorageSubSystemKey='' ||
            '''''''' || boxrec.cimppkey || '''''''' ||
            '' WHERE StorageSubSystemKey='' ||
            '''''''' || boxrec.ssskey ||'''''''' || '';'';
        EXECUTE tmpString;
        DELETE FROM tmp_LogicalDevice;
        INSERT INTO tmp_LogicalDevice
        SELECT key,removable,deviceid,storagesubsystemkey
        FROM LogicalDevice;
        DELETE FROM LogicalDevice;
        INSERT INTO LogicalDevice 
        SELECT DISTINCT ON(key, StorageSubSystemKey, Removable)
               key,removable,deviceid,storagesubsystemkey
        FROM tmp_LogicalDevice;
        tmpString :=
            ''UPDATE Volume '' ||
            ''SET StorageSubSystemKey='' ||
            '''''''' || boxrec.cimppkey || '''''''' ||
            '' WHERE StorageSubSystemKey='' ||
            '''''''' || boxrec.ssskey ||'''''''' || '';'';
        EXECUTE tmpString;
        DELETE FROM tmp_volume;
        INSERT INTO tmp_volume
        SELECT key,removable,deviceid,capacity,storagesubsystemkey
        FROM Volume;
        DELETE FROM Volume;
        INSERT INTO Volume 
        SELECT DISTINCT ON(key, StorageSubSystemKey, Removable)
               key,removable,deviceid,capacity,storagesubsystemkey
        FROM tmp_Volume;
    END LOOP;
    -- Starting deleting
    FOR boxrec IN
        SELECT tablename,
               tablekey
        FROM   PorscheDeleteTable
    LOOP
        tmpString := 
            ''DELETE FROM '' || boxrec.tablename ||
            '' WHERE key = '' ||
            '''''''' || boxrec.tablekey || '''''''';
        EXECUTE tmpString;
    END LOOP;    
    -- Clean up the StorageCluster as it is not used but confuse topo engine.
    EXECUTE ''DELETE FROM SubSystemCluster'';
    RETURN 1;
END;
' LANGUAGE 'plpgsql';


-- convert HostCluster into ModelNode for Topology
CREATE FUNCTION  get_HostCluster() RETURNS INTEGER AS '
BEGIN
    INSERT INTO ModelNode 
    SELECT 
           hc.key                               AS key,
           ''com.sun.netstorage.mgmt.component.model.domain.HostCluster'' 
						AS type,
           hc.name                              AS name,
	   NULL					AS vendor,
	   NULL					AS model,
	   NULL					AS parent,
	   NULL					AS status,
   	   NULL					AS state,
	   NULL					AS wwn,
	   NULL					AS number,
  	   NULL					AS attachedids,
	   NULL					AS childids
    FROM 
           HostCluster                          AS hc;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

-- convert SubSybSystemCluster into ModelNode for Topology
CREATE FUNCTION  get_SubSystemCluster() RETURNS INTEGER AS '
BEGIN
    INSERT INTO ModelNode 
    SELECT 
           ssc.key                              AS key,
	   ''com.sun.netstorage.mgmt.component.model.domain.SubSystemCluster'' 
           					AS type,
           ssc.name                             AS name,
	   NULL					AS vendor,
	   NULL					AS model,
	   NULL					AS parent,
	   NULL					AS status,
   	   NULL					AS state,
	   NULL					AS wwn,
	   NULL					AS number,
  	   NULL					AS attachedids,
	   NULL					AS childids
    FROM 
           SubSystemCluster                     AS ssc;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

-- convert Fabric into ModelNode for Topology
CREATE FUNCTION  get_Fabric() RETURNS INTEGER AS '
BEGIN
    INSERT INTO ModelNode 
    SELECT 
           fab.key                              AS key,
           ''com.sun.netstorage.mgmt.component.model.domain.Fabric'' 
						AS type,
           fab.name                             AS name,
	   NULL					AS vendor,
	   NULL					AS model,
	   NULL					AS parent,
	   NULL					AS status,
   	   NULL					AS state,
	   NULL					AS wwn,
	   NULL					AS number,
  	   NULL					AS attachedids,
	   NULL					AS childids
    FROM 
           Fabric                               AS fab;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

-- convert Switch into ModelNode for Topology
CREATE FUNCTION  get_Switch() RETURNS INTEGER AS '
BEGIN
    INSERT INTO ModelNode 
    SELECT 
           sw.key                               AS key,
           ''com.sun.netstorage.mgmt.component.model.domain.Switch'' 
                                                AS type,
           sw.name                              AS name,
           sw.vendor                            AS vendor,
           sw.model                             AS model,
           sw.fabrickey                         AS parent,
           sw.status                            AS status,
   	   NULL					AS state,
           sw.wwn                               AS wwn,
	   NULL					AS number,
  	   NULL					AS attachedids,
	   NULL					AS childids
    FROM 
           Switch                               AS sw;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

-- convert Zone into ModelNode for Topology
CREATE FUNCTION  get_Zone() RETURNS INTEGER AS '
BEGIN
    INSERT INTO ModelNode 
    SELECT 
           zn.key                               AS key,
           ''com.sun.netstorage.mgmt.component.model.domain.Zone'' 
                                                AS type,
           zn.name                              AS name,
	   NULL					AS vendor,
	   NULL					AS model,
           zn.fabrickey                         AS parent,
	   NULL					AS status,
   	   NULL					AS state,
	   NULL					AS wwn,
	   NULL					AS number,
  	   NULL					AS attachedids,
	   NULL					AS childids
    FROM 
           Zone                         AS zn;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

-- convert StorageHost into ModelNode for Topology
CREATE FUNCTION  get_StorageHost() RETURNS INTEGER AS '
BEGIN
    INSERT INTO ModelNode 
    SELECT 
           sh.key                               AS key,
           ''com.sun.netstorage.mgmt.component.model.domain.StorageHost'' 
                                                AS type,
           sh.hostname                          AS name,
	   NULL					AS vendor,
           sh.platform                          AS model,
           sh.hostclusterkey                    AS parent,
	   NULL					AS status,
   	   NULL					AS state,
	   NULL					AS wwn,
	   NULL					AS number,
  	   NULL					AS attachedids,
	   NULL					AS childids
    FROM 
           StorageHost                          AS sh;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

-- convert StorageSubSystem into ModelNode for Topology
CREATE FUNCTION  get_StorageSubSystem() RETURNS INTEGER AS '
BEGIN
    INSERT INTO ModelNode 
    SELECT 
           sss.key                              AS key,
           ''com.sun.netstorage.mgmt.component.model.domain.StorageSubSystem'' 
                                                AS type,
           sss.name                             AS name,
           sss.vendor                           AS vendor,
           sss.model                            AS model,
           sss.subsystemclusterkey              AS parent,
           sss.status                           AS status,
   	   NULL					AS state,
	   NULL					AS wwn,
	   NULL					AS number,
  	   NULL					AS attachedids,
	   NULL					AS childids
    FROM 
           StorageSubSystem                     AS sss;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

-- convert HBA into ModelNode for Topology
CREATE FUNCTION  get_HBA() RETURNS INTEGER AS '
BEGIN
    INSERT INTO ModelNode 
    SELECT 
           hba.key                              AS key,
           ''com.sun.netstorage.mgmt.component.model.domain.HBA'' 
                                                AS type,
           hba.controllernum                    AS name,
           hba.vendor                           AS vendor,
           hba.model                            AS model,
           hba.storagehostkey                   AS parent,
           hba.status                           AS status,
   	   NULL					AS state,
	   NULL					AS wwn,
	   NULL					AS number,
  	   NULL					AS attachedids,
	   NULL					AS childids
    FROM 
           HBA                                  AS hba;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

-- convert NxPort into ModelNode for Topology
CREATE FUNCTION  get_NxPort() RETURNS INTEGER AS '
BEGIN
    INSERT INTO ModelNode 
    SELECT 
           np.key                               AS key,
           ''com.sun.netstorage.mgmt.component.model.domain.NxPort'' 
                                                AS type,
           np.wwn                               AS name,
	   NULL					AS vendor,
           NULL					AS model,
           CASE WHEN np.hbakey NOTNULL
		THEN np.hbakey		
		ELSE np.storagesubsystemkey
		END				AS parent,
           CASE   WHEN np.removable
                  THEN ''Lost Comm''
                  ELSE ''OK''
                  END                           AS status,
           np.state                             AS state,
           np.wwn                               AS wwn,
	   NULL					AS number,
  	   NULL					AS attachedids,
	   NULL					AS childids
    FROM 
           NxPort                               AS np;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

-- convert SwitchPort into ModelNode for Topology
CREATE FUNCTION  get_SwitchPort() RETURNS INTEGER AS '
BEGIN
    INSERT INTO ModelNode 
    SELECT 
           swp.key                               AS key,
           ''com.sun.netstorage.mgmt.component.model.domain.SwitchPort'' 
                                                AS type,
           swp.wwn                              AS name,
	   NULL					AS vendor,
           NULL					AS model,
           swp.switchkey			AS parent,
	   NULL 				AS status,
           swp.state                            AS state,
           swp.wwn                              AS wwn,
	   swp.number				AS number,
  	   NULL					AS attachedids,
	   NULL					AS childids
    FROM 
           SwitchPort                        	AS swp;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

-- Add attached port id to a ModelNode for Topology 
CREATE FUNCTION get_AttachedIds() RETURNS INTEGER AS '
DECLARE 
    rec			RECORD;
    rec2		RECORD;
    updateString 	VARCHAR;
    count		INTEGER;
BEGIN
    FOR rec IN  
        SELECT 
               ap.portkey		AS portkey,
               ap.attachedportkey	AS attachedportkey,
      	       ap.removable		AS removable
        FROM 	
	       AttachedPort	AS ap	
    LOOP
	FOR rec2 IN
            SELECT 
                   mn.key		AS key,
                   mn.attachedids 	AS attachedids
            FROM ModelNode 		AS mn
            WHERE mn.key = rec.portkey
	LOOP
	    updateString := ''UPDATE ModelNode '';
            count := count_StringArray(rec2.attachedids);
	    IF count = 0
            THEN
	       updateString := updateString ||  
	          ''SET attachedids = '' || '''''''' || ''{"'' ||
                  '''' || rec.attachedportkey || '''' || ''"}'' || '''''''';
 	    ELSE
	       updateString := updateString ||
		  ''SET attachedids['' ||  count+1 || ''] = '' ||
		  '''''''' || rec.attachedportkey || '''''''';
	    END IF;
               IF rec.removable
 	    THEN
               updateString := updateString || '', '' ||
		               ''status = ''''Lost Comm'''''';
            END IF;
            updateString := updateString || '' WHERE key = '' ||
                            '''''''' || rec2.key || '''''''' || '';''; 
 	    EXECUTE updateString;
        END LOOP;
    END LOOP;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

-- Add child id to a ModelNode for Topology 
CREATE FUNCTION get_ChildIds() RETURNS INTEGER AS '
DECLARE 
    rec			RECORD;
    rec2		RECORD;
    updateString 	VARCHAR;
    count		INTEGER;
BEGIN
    FOR rec IN  
        SELECT key	AS key,
	       parent 	AS parent
        FROM ModelNode 
        WHERE parent NOTNULL
    LOOP
	FOR rec2 IN
            SELECT 
                   mn.key		AS key,
                   mn.childids	 	AS childids
            FROM ModelNode 		AS mn
            WHERE mn.key = rec.parent
	LOOP
            updateString := ''UPDATE ModelNode ''; 
            count := count_StringArray(rec2.childids);
	    IF count = 0
            THEN
               updateString := updateString ||  
	          ''SET childids = '' || '''''''' || ''{"'' ||
                  '''' || rec.key || '''' || ''"}'' || '''''''';
            ELSE
	       updateString := updateString ||
	          ''SET childids['' ||  count+1 || ''] = '' ||
	          '''''''' || rec.key || '''''''';
 	    END IF;
	     updateString := updateString || '' WHERE key = '' ||
		'''''''' || rec.parent || '''''''' || '';''; 

	    EXECUTE updateString;
       	END LOOP;
    END LOOP;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';


-- Since UNION doesn't support array type column, we have to create
-- a temp table.  
CREATE TABLE MixPortTemp (
	key	VARCHAR,
 	zonekey VARCHAR[]
    );

-- Add zone child id to a ModelNode for Topology 
CREATE FUNCTION get_ZoneChildIds() RETURNS INTEGER AS '
DECLARE 
    rec			RECORD;
    rec2		RECORD;
    updateString 	VARCHAR;
    count		INTEGER;
    count2              INTEGER;
    countZoneKey        INTEGER;
BEGIN
    DELETE FROM MixPortTemp;
    INSERT INTO MixPortTemp
    SELECT nxp.key		AS key,
	   nxp.zonekey 		AS zonekey
    FROM   NxPort		AS nxp   
    WHERE  nxp.zonekey NOTNULL;
    INSERT INTO MixPortTemp
    SELECT swp.key		AS key,
	   swp.zonekey 		AS zonekey
    FROM   SwitchPort		AS swp   
    WHERE  swp.zonekey NOTNULL;

    FOR rec IN  
	SELECT mp.key 		AS key,
	       mp.zonekey 	AS zonekey
	FROM   MixPortTemp	AS mp
    LOOP
        count := 1;
	countZoneKey := count_StringArray(rec.zonekey);
	LOOP
-- assuming that there is no null element in zonekey array
           IF count <= countZoneKey 
 	   THEN  
              FOR rec2 IN
                 SELECT 
                        mn.key		AS key,
                        mn.childids	AS childids
                 FROM ModelNode 		as mn
                 WHERE rec.zonekey[count] = mn.key
              LOOP
		 updateString := ''UPDATE ModelNode '';
                 count2 := count_StringArray(rec2.childids);
 		 IF count2 = 0
                 THEN
                    updateString :=  updateString ||
	               ''SET childids = '' || '''''''' || ''{"'' ||
                       '''' || rec.key || '''' || ''"}'' || '''''''';
                 ELSE
	            updateString := updateString || 
		       ''SET childids['' ||  count2+1 || ''] = '' ||
		       '''''''' || rec.key || '''''''';
 		 END IF;
		 updateString := updateString || '' WHERE key = '' ||
		    '''''''' || rec.zonekey[count] || '''''''' || '';''; 
 	         EXECUTE updateString;
              END LOOP;
    
              count := count + 1;
           ELSE
	      EXIT;
           END IF;
	END LOOP;
    END LOOP;
    RETURN 1;
END;
' LANGUAGE 'plpgsql';


-- At the end of discovery, this function will be called by 
-- refresh_virtualtables to give Topology a fresh view
-- of ModelNodes.
CREATE FUNCTION  refresh_ModelNode() RETURNS INTEGER AS '
BEGIN
    DELETE FROM ModelNode;    
    PERFORM get_HostCluster();
    PERFORM get_StorageHost();
    PERFORM get_HBA();
    PERFORM get_SubSystemCluster();
    PERFORM get_StorageSubSystem();
    PERFORM get_fabric();
    PERFORM get_Switch();
    PERFORM get_Zone();
    PERFORM get_NxPort();
    PERFORM get_SwitchPort();
    PERFORM get_ChildIds();
    PERFORM get_ZoneChildIds();
    PERFORM get_AttachedIds();
    RETURN 1;
END;
' LANGUAGE 'plpgsql';

/* count the elements in an integer array 
   Input param: anArray - an integer array
   Output: The count of elements in the array
   Note: if an array has null elements, then this function
   will return the position before the first null element.
   If the an array has NO null elements, then this function
   returns the count of elements in an array.
*/
CREATE FUNCTION count_IntegerArray(_INT4) RETURNS INTEGER AS '
DECLARE
    anArray ALIAS FOR $1;
    counter INTEGER;
BEGIN
    counter := 0;
    LOOP
        IF ( anArray[counter+1] ISNULL ) 
        THEN
            EXIT;  -- Either the array is null or reached the end, exit loop
        END IF;
        counter := counter + 1;
    END LOOP;
    RETURN counter;
END;
' LANGUAGE 'plpgsql';


/* count the elements in a String array. 
   Input param: anArray - an String array
   Output: The count of elements in the array
   Note: if an array has null elements, then this function
   will return the position before the first null element.
   If the an array has NO null elements, then this function
*/
CREATE FUNCTION count_StringArray(_VARCHAR) RETURNS INTEGER AS '
DECLARE
    anArray ALIAS FOR $1;
    counter INTEGER;
BEGIN
    counter := 0;
    LOOP
        IF ( anArray[counter+1] ISNULL ) 
        THEN
            EXIT;  -- Either the array is null or reached the end, exit loop
        END IF;
        counter := counter + 1;
    END LOOP;
    RETURN counter;
END;
' LANGUAGE 'plpgsql';


/*
   SQL code to define the new array iterator functions and operators
   define the array operators *=, **=, *~ and **~ for type _text
*/
create function array_texteq(_text, text) returns bool
  as '/opt/SUNWnsm/util/pgsql/nsm1/lib/array_iterator.so' 
  language 'c';

create function array_all_texteq(_text, text) returns bool
  as '/opt/SUNWnsm/util/pgsql/nsm1/lib/array_iterator.so' 
  language 'c';

create function array_textregexeq(_text, text) returns bool
  as '/opt/SUNWnsm/util/pgsql/nsm1/lib/array_iterator.so' 
  language 'c';

create function array_all_textregexeq(_text, text) returns bool
  as '/opt/SUNWnsm/util/pgsql/nsm1/lib/array_iterator.so' 
  language 'c';

create operator *= (
  leftarg=_text, 
  rightarg=text, 
  procedure=array_texteq);

create operator **= (
  leftarg=_text,
  rightarg=text,
  procedure=array_all_texteq);

create operator *~ (
  leftarg=_text,
  rightarg=text,
  procedure=array_textregexeq);

create operator **~ (
  leftarg=_text,
  rightarg=text,
  procedure=array_all_textregexeq);


-- define the array operators *=, **=, *~ and **~ for type _varchar
--
-- NOTE: "varchar" is also a reserved word and must be quoted.
--
create function array_varchareq(_varchar, varchar) returns bool
  as '/opt/SUNWnsm/util/pgsql/nsm1/lib/array_iterator.so' 
  language 'c';

create function array_all_varchareq(_varchar, varchar) returns bool
  as '/opt/SUNWnsm/util/pgsql/nsm1/lib/array_iterator.so' 
  language 'c';

create function array_varcharregexeq(_varchar, varchar) returns bool
  as '/opt/SUNWnsm/util/pgsql/nsm1/lib/array_iterator.so' 
  language 'c';

create function array_all_varcharregexeq(_varchar, varchar) returns bool
  as '/opt/SUNWnsm/util/pgsql/nsm1/lib/array_iterator.so' 
  language 'c';

create operator *= (
  leftarg=_varchar, 
  rightarg="varchar", 
  procedure=array_varchareq);

create operator **= (
  leftarg=_varchar,
  rightarg="varchar",
  procedure=array_all_varchareq);

create operator *~ (
  leftarg=_varchar,
  rightarg="varchar",
  procedure=array_varcharregexeq);

create operator **~ (
  leftarg=_varchar,
  rightarg="varchar",
  procedure=array_all_varcharregexeq);


-- define the array operators *=, **=, *~ and **~ for type _bpchar
--
create function array_bpchareq(_bpchar, bpchar) returns bool
  as '/opt/SUNWnsm/util/pgsql/nsm1/lib/array_iterator.so' 
  language 'c';

create function array_all_bpchareq(_bpchar, bpchar) returns bool
  as '/opt/SUNWnsm/util/pgsql/nsm1/lib/array_iterator.so' 
  language 'c';

create function array_bpcharregexeq(_bpchar, bpchar) returns bool
  as '/opt/SUNWnsm/util/pgsql/nsm1/lib/array_iterator.so' 
  language 'c';

create function array_all_bpcharregexeq(_bpchar, bpchar) returns bool
  as '/opt/SUNWnsm/util/pgsql/nsm1/lib/array_iterator.so' 
  language 'c';

create operator *= (
  leftarg=_bpchar, 
  rightarg=bpchar, 
  procedure=array_bpchareq);

create operator **= (
  leftarg=_bpchar,
  rightarg=bpchar,
  procedure=array_all_bpchareq);

create operator *~ (
  leftarg=_bpchar,
  rightarg=bpchar,
  procedure=array_bpcharregexeq);

create operator **~ (
  leftarg=_bpchar,
  rightarg=bpchar,
  procedure=array_all_bpcharregexeq);


-- define the array operators *=, **=, *> and **> for type _int4
--
create function array_int4eq(_int4, int4) returns bool
  as '/opt/SUNWnsm/util/pgsql/nsm1/lib/array_iterator.so' 
  language 'c';

create function array_all_int4eq(_int4, int4) returns bool
  as '/opt/SUNWnsm/util/pgsql/nsm1/lib/array_iterator.so' 
  language 'c';

create function array_int4ne(_int4, int4) returns bool
  as '/opt/SUNWnsm/util/pgsql/nsm1/lib/array_iterator.so' 
  language 'c';

create function array_all_int4ne(_int4, int4) returns bool
  as '/opt/SUNWnsm/util/pgsql/nsm1/lib/array_iterator.so' 
  language 'c';

create function array_int4gt(_int4, int4) returns bool
  as '/opt/SUNWnsm/util/pgsql/nsm1/lib/array_iterator.so' 
  language 'c';

create function array_all_int4gt(_int4, int4) returns bool
  as '/opt/SUNWnsm/util/pgsql/nsm1/lib/array_iterator.so' 
  language 'c';

create function array_int4ge(_int4, int4) returns bool
  as '/opt/SUNWnsm/util/pgsql/nsm1/lib/array_iterator.so' 
  language 'c';

create function array_all_int4ge(_int4, int4) returns bool
  as '/opt/SUNWnsm/util/pgsql/nsm1/lib/array_iterator.so' 
  language 'c';

create function array_int4lt(_int4, int4) returns bool
  as '/opt/SUNWnsm/util/pgsql/nsm1/lib/array_iterator.so' 
  language 'c';

create function array_all_int4lt(_int4, int4) returns bool
  as '/opt/SUNWnsm/util/pgsql/nsm1/lib/array_iterator.so' 
  language 'c';

create function array_int4le(_int4, int4) returns bool
  as '/opt/SUNWnsm/util/pgsql/nsm1/lib/array_iterator.so' 
  language 'c';

create function array_all_int4le(_int4, int4) returns bool
  as '/opt/SUNWnsm/util/pgsql/nsm1/lib/array_iterator.so' 
  language 'c';

create operator *= (
  leftarg=_int4,
  rightarg=int4,
  procedure=array_int4eq);

create operator **= (
  leftarg=_int4,
  rightarg=int4,
  procedure=array_all_int4eq);

create operator *<> (
  leftarg=_int4,
  rightarg=int4,
  procedure=array_int4ne);

create operator **<> (
  leftarg=_int4,
  rightarg=int4,
  procedure=array_all_int4ne);

create operator *> (
  leftarg=_int4,
  rightarg=int4,
  procedure=array_int4gt);

create operator **> (
  leftarg=_int4,
  rightarg=int4,
  procedure=array_all_int4gt);

create operator *>= (
  leftarg=_int4,
  rightarg=int4,
  procedure=array_int4ge);

create operator **>= (
  leftarg=_int4,
  rightarg=int4,
  procedure=array_all_int4ge);

create operator *< (
  leftarg=_int4,
  rightarg=int4,
  procedure=array_int4lt);

create operator **< (
  leftarg=_int4,
  rightarg=int4,
  procedure=array_all_int4lt);

create operator *<= (
  leftarg=_int4,
  rightarg=int4,
  procedure=array_int4le);

create operator **<= (
  leftarg=_int4,
  rightarg=int4,
  procedure=array_all_int4le);

-- define the array operators *=, **<>  for type _oid  (added tobias 1. 1999)
--
create function array_oideq(_oid, oid) returns bool
  as '/opt/SUNWnsm/util/pgsql/nsm1/lib/array_iterator.so' 
  language 'c';

create function array_all_oidne(_oid, oid) returns bool
  as '/opt/SUNWnsm/util/pgsql/nsm1/lib/array_iterator.so' 
  language 'c';

create operator *= (
  leftarg=_oid, 
  rightarg=oid, 
  procedure=array_oideq);

create operator **<> (
  leftarg=_oid,
  rightarg=oid,
  procedure=array_all_oidne);





