Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- F5 в pgAdmin
- SET search_path TO _00000003, public;
- DO $$
- DECLARE
- vStep text := '300';
- vCurLead integer := 0;
- vNextLead integer;
- vDeadline timestamp := '"2016-10-01 05:40:00.000000+03"'::timestamp with time zone;
- vCountDel integer;
- vTotalCount integer := 0;
- BEGIN
- --PERFORM dblink_connect('crm', 'dbname=inside.tensor.ru port=5432 host=ins-db-master.unix.tensor.ru user= password=');
- PERFORM dblink_connect('crm', 'dbname=' || current_database()::text || ' port=' || inet_server_port());
- RAISE NOTICE '%: ЗАПУЩЕНО. Заполнение поля "Регламент" таблицы "Лид", если оно пустое (удаление лида)', date_trunc('second', clock_timestamp())::TEXT;
- LOOP
- SELECT maxID
- INTO vNextLead
- FROM dblink('crm', 'SET search_path TO _00000003, public;
- WITH
- main_sel AS (
- SELECT
- l."@Документ" "Документ",
- (
- SELECT
- e."Регламент"
- FROM "Событие" e
- WHERE
- e."@Событие" = l."ПоследнийКонтакт"
- ) "Регламент"
- FROM "Лид" l
- WHERE
- l."Регламент" IS NULL AND
- l."Черновик" IS NULL AND
- l."ПоследнийКонтакт" IS NOT NULL AND
- l."@Документ" > ' || vCurLead::TEXT || '
- ORDER BY l."@Документ"
- LIMIT ' || vStep || '
- ),
- updateCTE AS (
- UPDATE "Лид" l SET "Регламент" = m."Регламент"
- FROM main_sel m
- WHERE
- l."@Документ" = m."Документ"
- RETURNING l."@Документ"
- ),
- SELECT MAX("@Документ") maxID FROM updateCTE;
- ') t (maxID integer);
- EXIT WHEN (vNextLead IS NULL OR clock_timestamp() > vDeadline);
- vCurLead := vNextLead;
- vTotalCount := vTotalCount + 1;
- --RAISE NOTICE '%: №: %, ID %', date_trunc('second', clock_timestamp())::text, vTotalCount, vNextLead;
- END LOOP;
- RAISE NOTICE '%: №: %', date_trunc('second', clock_timestamp())::text, vTotalCount;
- IF NOT clock_timestamp() > vDeadline THEN
- RAISE NOTICE '%: ЗАПУЩЕНО. Удаление лида в случае пустых полей "ПоследнийКонтакт", "НазваниеКлиента" и "КонтактныеДанные".', date_trunc('second', clock_timestamp())::TEXT;
- SELECT countDel
- INTO vCountDel
- FROM dblink('crm', 'SET search_path TO _00000003, public;
- WITH
- deleteCTE AS (
- DELETE FROM "Лид"
- WHERE
- "Черновик" IS NULL AND
- "ПоследнийКонтакт" IS NULL AND
- "НазваниеКлиента" IS NULL AND
- "КонтактныеДанные" IS NULL
- RETURNING "@Документ"
- ),
- SELECT count(1) countDel FROM deleteCTE;
- ') t (countDel integer);
- --RAISE NOTICE '%: Del %', date_trunc('second', clock_timestamp())::text, vCountDel;
- END IF;
- PERFORM dblink_disconnect('crm');
- IF clock_timestamp() > vDeadline THEN
- RAISE NOTICE '!!! Выход по таймауту: ID текущего лида %.', vNextLead;
- ELSE
- RAISE NOTICE '%: ЗАВЕРШЕНО. №: %, Del %', date_trunc('second', clock_timestamp())::text, vTotalCount;
- END IF;
- EXCEPTION WHEN others THEN
- PERFORM dblink_disconnect('crm');
- RAISE NOTICE '% %', SQLERRM, SQLSTATE;
- RAISE NOTICE '%: Выход по ошибке: №: %, ID текущего лида %', date_trunc('second', clock_timestamp())::text, vTotalCount, vNextLead;
- RAISE;
- END; $$
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement