Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- schema = context['schema'] #'_00000003'
- vStep = 100
- SqlQuery('''
- -- Создаем временный индекс
- do $$
- begin
- dblink(''CREATE INDEX CONCURRENTLY "iЛид-РегламентДокументСозданиеДатаNULL_Tmp"
- ON {schema}."Лид"
- USING btree
- ("Регламент", "@Документ")
- WHERE "СозданиеДата" IS NULL;'')
- end;
- $$;
- '''.format(schema = schema))
- resTheme = SqlQuery('''
- WITH recursive Regs as (
- SELECT
- x."@Регламент" "Регламент",
- x."Раздел@"
- FROM {schema}."Регламент" x
- WHERE "Раздел" = vMainReg
- UNION ALL
- SELECT
- r."@Регламент" "Регламент",
- r."Раздел@"
- FROM Regs t
- INNER JOIN {schema}."Регламент" r
- ON t."Регламент" = r."Раздел")
- SELECT "Регламент"
- FROM Regs
- WHERE "Раздел@" IS NOT TRUE
- '''.format(schema = schema))
- for rec in resTheme:
- vCurLead = 0
- while True:
- vNextLead = SqlQueryScalar('''
- SELECT max_id
- FROM dblink(''
- SET search_path TO "_00000003", public;
- WITH
- sel AS (
- SELECT
- l."@Документ"
- FROM "Лид" l
- WHERE
- l."Регламент" = {vTheme} AND
- l."@Документ" > {vCurLead} '' AND
- l."СозданиеДата" IS NULL
- ORDER BY l."Регламент", l."@Документ"
- LIMIT {vStep}
- ),
- upd AS (
- UPDATE "Лид" l
- SET
- "СозданиеДата" = date_trunc('' || ''''''day'''''' || '', l."ДатаСоздания")
- FROM sel d
- WHERE
- l."@Документ" = d."@Документ"
- RETURNING l."@Документ"
- )
- SELECT max("@Документ") max_id
- FROM upd;
- '') t ;
- '''.format(vTheme=vTheme, vCurLead=vCurLead, vStep=vStep))
- if vNextLead is Null:
- break
- vCurLead = vNextLead
- SqlQuery('''
- -- Удаление временного индекса
- do $$
- begin
- loop
- begin
- LOCK TABLE {schema}."Лид" IN ACCESS EXCLUSIVE MODE NOWAIT;
- DROP INDEX {schema}."iЛид-РегламентДокументСозданиеДатаNULL_Tmp";
- exception when lock_not_available then
- continue;
- end;
- exit;
- end loop;
- end;
- $$;
- ''').format(schema = schema))
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement