|
-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 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 <<
|
