
    DROP VIEW IF EXISTS searchMaterialView;


    CREATE OR REPLACE VIEW searchMaterialView AS
    (
        (SELECT gtcMaterialControl.controlNumber as controlNumber,
                gtcMaterialControl.entrancedate,
                gtcMaterialControl.lastchangedate,
                gtcMaterialControl.category,
                gtcMaterialControl.level,
                gtcMaterialControl.materialgenderid,
                gtcMaterialControl.materialtypeid,
                gtcMaterialControl.materialphysicaltypeid,
                gtcExemplaryControl.itemnumber as exemplaryitemnumber,
                gtcExemplaryControl.originallibraryunitid as exemplaryoriginallibraryunitid,
                gtcExemplaryControl.libraryunitid as exemplarylibraryunitid,
                gtcExemplaryControl.acquisitiontype as exemplaryacquisitiontype,
                gtcExemplaryControl.exemplarystatusid as exemplaryexemplarystatusid,
                gtcExemplaryControl.materialgenderid as exemplarymaterialgenderid,
                gtcExemplaryControl.materialtypeid as exemplarymaterialtypeid,
                gtcExemplaryControl.materialphysicaltypeid as exemplarymaterialphysicaltypeid,
                gtcExemplaryControl.entrancedate as exemplaryentrancedate,
                gtcExemplaryControl.lowdate as exemplarylowdate
        FROM   gtcMaterialControl LEFT JOIN gtcExemplaryControl USING (controlNumber))
    UNION
        (SELECT gtcMaterialControl.controlNumberFather as controlNumber,
                gtcMaterialControl.entrancedate,
                gtcMaterialControl.lastchangedate,
                gtcMaterialControl.category,
                gtcMaterialControl.level,
                gtcMaterialControl.materialgenderid,
                gtcMaterialControl.materialtypeid,
                gtcMaterialControl.materialphysicaltypeid,
                gtcExemplaryControl.itemnumber as exemplaryitemnumber,
                gtcExemplaryControl.originallibraryunitid as exemplaryoriginallibraryunitid,
                gtcExemplaryControl.libraryunitid as exemplarylibraryunitid,
                gtcExemplaryControl.acquisitiontype as exemplaryacquisitiontype,
                gtcExemplaryControl.exemplarystatusid as exemplaryexemplarystatusid,
                gtcExemplaryControl.materialgenderid as exemplarymaterialgenderid,
                gtcExemplaryControl.materialtypeid as exemplarymaterialtypeid,
                gtcExemplaryControl.materialphysicaltypeid as exemplarymaterialphysicaltypeid,
                gtcExemplaryControl.entrancedate as exemplaryentrancedate,
                gtcExemplaryControl.lowdate as exemplarylowdate
        FROM   gtcMaterialControl LEFT JOIN gtcExemplaryControl USING (controlNumber) WHERE gtcMaterialControl.controlNumberFather IS NOT NULL)
    UNION
        (SELECT  A.controlNumberFather as controlNumber,
                B.entrancedate,
                B.lastchangedate,
                B.category,
                B.level,
                B.materialgenderid,
                B.materialtypeid,
                B.materialphysicaltypeid,
                C.itemnumber as exemplaryitemnumber,
                C.originallibraryunitid as exemplaryoriginallibraryunitid,
                C.libraryunitid as exemplarylibraryunitid,
                C.acquisitiontype as exemplaryacquisitiontype,
                C.exemplarystatusid as exemplaryexemplarystatusid,
                C.materialgenderid as exemplarymaterialgenderid,
                C.materialtypeid as exemplarymaterialtypeid,
                C.materialphysicaltypeid as exemplarymaterialphysicaltypeid,
                C.entrancedate as exemplaryentrancedate,
                C.lowdate as exemplarylowdate
        FROM    gtcMaterialControl   A
        LEFT JOIN gtcMaterialControl B ON (B.controlNumber = A.controlNumberFather)
        LEFT JOIN gtcExemplaryControl C ON (A.controlnumber = C.controlnumber) WHERE A.controlNumberFather IS NOT NULL)
    UNION
        (SELECT gtcMaterialControl.controlNumber as controlNumber,
                gtcMaterialControl.entrancedate,
                gtcMaterialControl.lastchangedate,
                gtcMaterialControl.category,
                gtcMaterialControl.level,
                gtcMaterialControl.materialgenderid,
                gtcMaterialControl.materialtypeid,
                gtcMaterialControl.materialphysicaltypeid,
                gtcExemplaryControl.itemnumber as exemplaryitemnumber,
                gtcExemplaryControl.originallibraryunitid as exemplaryoriginallibraryunitid,
                gtcExemplaryControl.libraryunitid as exemplarylibraryunitid,
                gtcExemplaryControl.acquisitiontype as exemplaryacquisitiontype,
                gtcExemplaryControl.exemplarystatusid as exemplaryexemplarystatusid,
                gtcExemplaryControl.materialgenderid as exemplarymaterialgenderid,
                gtcExemplaryControl.materialtypeid as exemplarymaterialtypeid,
                gtcExemplaryControl.materialphysicaltypeid as exemplarymaterialphysicaltypeid,
                gtcExemplaryControl.entrancedate as exemplaryentrancedate,
                gtcExemplaryControl.lowdate as exemplarylowdate
        FROM gtcmaterialcontrol INNER JOIN gtcexemplarycontrol ON (gtcmaterialcontrol.controlnumberfather = gtcexemplarycontrol.controlnumber))

    );


    DROP TABLE IF EXISTS gtcSearchMaterialView;
    CREATE TABLE    gtcSearchMaterialView as SELECT * FROM searchMaterialView;

    BEGIN;
        DELETE FROM gtcSearchMaterialView;
        INSERT INTO gtcSearchMaterialView SELECT * FROM searchMaterialView;
    COMMIT;


    DROP TABLE  IF EXISTS gtcSearchTableUpdateControl;
    CREATE TABLE    gtcSearchTableUpdateControl ( lastUpdate timestamp);
    DELETE FROM     gtcSearchTableUpdateControl;
    INSERT INTO     gtcSearchTableUpdateControl (lastUpdate) values (now());

    DROP TRIGGER  IF EXISTS gtcTrig_updateSearchMaterialTable ON gtcMaterial;

    CREATE OR REPLACE FUNCTION gtcFnc_updateSearchMaterialViewTable() RETURNS TRIGGER AS '
            BEGIN

                DELETE FROM gtcSearchMaterialView;
                INSERT INTO gtcSearchMaterialView SELECT * FROM searchMaterialView;

                DELETE FROM gtcSearchTableUpdateControl;
                INSERT INTO gtcSearchTableUpdateControl (lastUpdate) values (now());

                RETURN OLD;
            END;
        ' LANGUAGE 'plpgsql';

    CREATE TRIGGER gtcTrig_updateSearchMaterialTable AFTER INSERT OR DELETE OR UPDATE ON gtcMaterial FOR EACH ROW EXECUTE PROCEDURE gtcFnc_updateSearchMaterialViewTable();

    DROP FUNCTION gtcFnc_updateSearchMaterialViewTableBool();
    CREATE OR REPLACE FUNCTION gtcFnc_updateSearchMaterialViewTableBool() RETURNS BOOLEAN AS '
            DECLARE

                lastUpdate_     BOOLEAN;

            BEGIN

                SELECT INTO lastUpdate_ (lastUpdate + (interval ''0 hours 1 minutes'')) <= now() FROM gtcSearchTableUpdateControl ORDER BY lastUpdate DESC LIMIT 1;

                IF (lastUpdate_) THEN

                    DELETE FROM gtcSearchMaterialView;
                    INSERT INTO gtcSearchMaterialView SELECT * FROM searchMaterialView;

                    DELETE FROM gtcSearchTableUpdateControl;
                    INSERT INTO gtcSearchTableUpdateControl (lastUpdate) values (now());

                END IF;

                RETURN TRUE;

            END;
        ' LANGUAGE 'plpgsql';
