-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> begin func >>
CREATE OR REPLACE FUNCTION pg_temp.AddGroupId()
    RETURNS int AS
$func$

DECLARE
    _comp_ver  int  := 4;
    _comp_name text := 'mdb.ph_history';

BEGIN

    IF EXISTS(SELECT *
              FROM components
              WHERE name = _comp_name
                AND veri >= _comp_ver)
    THEN
        RAISE NOTICE 'Component: % already updated to version % or more', _comp_name, _comp_ver;
        RETURN 1;
    END IF;

-- ++++++++++++++++  begin payload  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    RAISE NOTICE 'Start component % update to version %', _comp_name, _comp_ver;

    ALTER TABLE ph_history
        ADD GroupID int NULL;
    RAISE NOTICE 'Column GroupID added to table PH_HISTORY';

    -- вычисляем значение для этой колонки
    UPDATE ph_history ph_hist
    SET GroupID =
            (SELECT MIN(ph_hist_sub.ID)
             FROM ph_history AS ph_hist_sub
             WHERE ph_hist_sub.PH_ID = ph_hist.PH_ID
               AND ph_hist_sub.ID <= ph_hist.ID
               AND ph_hist_sub.ID > ph_hist.ID - 100
               AND ph_hist_sub.UserName = ph_hist.UserName
               AND ph_hist_sub.HostName = ph_hist.HostName
               AND ABS(
                           DATE_PART('day', ph_hist_sub.ModifyDate - ph_hist.ModifyDate) * 24 +
                           DATE_PART('hour', ph_hist_sub.ModifyDate - ph_hist.ModifyDate) * 60 +
                           DATE_PART('minute', ph_hist_sub.ModifyDate - ph_hist.ModifyDate) * 60 +
                           DATE_PART('second', ph_hist_sub.ModifyDate - ph_hist.ModifyDate)
                   ) < 5)
    WHERE ph_hist.GroupID IS NULL;
    RAISE NOTICE 'GroupID calculated for existing items';

-- ++++++++++++++++++  end payload  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

    INSERT INTO components(name, veri) VALUES (_comp_name, _comp_ver);

    RAISE NOTICE '****Component: %  v: %  OK', _comp_name, _comp_ver;
    RETURN 1;

END
$func$ LANGUAGE plpgsql;
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< end func <<

-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> begin func >>
CREATE OR REPLACE FUNCTION pg_temp.ChangeDefaultHostNameConstraint()
    RETURNS int AS
$func$

DECLARE
    _comp_ver  int  := 5;
    _comp_name text := 'mdb.ph_history';

BEGIN

    IF EXISTS(SELECT *
              FROM components
              WHERE name = _comp_name
                AND veri >= _comp_ver)
    THEN
        RAISE NOTICE 'Component: % already updated to version % or more', _comp_name, _comp_ver;
        RETURN 1;
    END IF;

-- ++++++++++++++++  begin payload  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    RAISE NOTICE 'Start component % update to version %', _comp_name, _comp_ver;

    ALTER TABLE ph_history
        ALTER COLUMN HostName SET DEFAULT host(inet_client_addr());

-- ++++++++++++++++++  end payload  ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

    INSERT INTO components(name, veri) VALUES (_comp_name, _comp_ver);

    RAISE NOTICE '****Component: %  v: %  OK', _comp_name, _comp_ver;
    RETURN 1;

END
$func$ LANGUAGE plpgsql;
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< end func <<

-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> begin step >>
BEGIN TRANSACTION;
SELECT pg_temp.AddGroupId();
COMMIT;
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< end step <<

-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> begin step >>
BEGIN TRANSACTION;
SELECT pg_temp.ChangeDefaultHostNameConstraint();
COMMIT;
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< end step <<
