SET search_path to public; --DROP external table ext__federalproject; CREATE EXTERNAL TABLE ext__federalproject(body json) LOCATION ('gpfdist://airflow:17000/epbs/7710168360-federalproject/federalproject_200416_1631.json.gz') FORMAT 'TEXT' CREATE TEMP TABLE fp AS SELECT body FROM ext__federalproject; -- ProjectsFederalEntry --DROP TABLE federal_project; CREATE TABLE federal_project AS SELECT nullif(body ->> 'recordid', '')::int AS recordid, nullif(body ->> 'idnp', '')::int AS idnp, nullif(body ->> 'code', '')::char(2) AS code, nullif(body ->> 'npcode', '')::char(1) AS npcode, nullif(body ->> 'npname', '')::text AS npname, nullif(body ->> 'metaid', '')::int AS metaid, nullif(body ->> 'startdate', '')::timestamp AS startdate, nullif(body ->> 'enddate', '')::timestamp AS enddate, nullif(body ->> 'fullname', '')::text AS fullname, nullif(body ->> 'shortname', '')::text AS shortname, nullif(body ->> 'curator', '')::text AS curator, nullif(body ->> 'person', '')::text AS person, nullif(body ->> 'kvsr', '')::text AS kvsr, nullif(body ->> 'seniorofficial', '')::text AS seniorofficial, nullif(body ->> 'startdateproj', '')::date AS startdateproj, nullif(body ->> 'enddateproj', '')::date AS enddateproj, nullif(body ->> 'recordupdate', '')::timestamp AS recordupdate, now()::date AS load_date FROM ext__federalproject DISTRIBUTED BY (recordid); ALTER TABLE federal_project ADD CONSTRAINT pk_np_project PRIMARY KEY (recordid); COMMENT ON COLUMN federal_project.metaid IS 'Идентификатор федерального проекта'; COMMENT ON COLUMN federal_project.recordid IS 'Идентификатор федерального проекта'; COMMENT ON COLUMN federal_project.startdate IS 'Дата начала действия записи'; COMMENT ON COLUMN federal_project.enddate IS 'Дата окончания действия записи'; COMMENT ON COLUMN federal_project.code IS 'Код федерального проекта'; COMMENT ON COLUMN federal_project.fullname IS 'Полное наименование федерального проекта'; COMMENT ON COLUMN federal_project.shortname IS 'Сокращенное наименование федерального проекта'; COMMENT ON COLUMN federal_project.curator IS 'Куратор проекта'; COMMENT ON COLUMN federal_project.person IS 'Руководитель проекта'; COMMENT ON COLUMN federal_project.kvsr IS 'Администратор проекта'; COMMENT ON COLUMN federal_project.seniorofficial IS 'Старшее должностное лицо'; COMMENT ON COLUMN federal_project.startdateproj IS 'Дата начала федерального проекта'; COMMENT ON COLUMN federal_project.enddateproj IS 'Дата окончания федерального проекта'; COMMENT ON COLUMN federal_project.recordupdate IS 'Дата обновления записи'; COMMENT ON COLUMN federal_project.idnp IS 'Идентификатор национального проекта'; COMMENT ON COLUMN federal_project.npcode IS 'Код национального проекта'; COMMENT ON COLUMN federal_project.npname IS 'Наименование национального проекта'; -- ProjectsFederalEntryProgram -- DROP TABLE program CREATE TABLE program AS SELECT federal_project_recordid, nullif(program ->> 'recordid', '')::int AS recordid, program ->> 'name' AS name, program ->> 'code' AS code FROM ( SELECT nullif(body ->> 'recordid', '')::int AS federal_project_recordid, json_array_elements((body -> 'programs')::json) as program FROM ext__federalproject WHERE json_array_length(body -> 'programs') > 0 ) AS ps DISTRIBUTED BY (federal_project_recordid, recordid); COMMENT ON COLUMN program.recordid IS 'Идентификатор записи справочника «Государственные программы»'; COMMENT ON COLUMN program.name IS 'Наименование государственной программы'; COMMENT ON COLUMN program.code IS 'Код государственной программы'; -- ProjectsFederalEntryTarget -- DROP TABLE target; CREATE TABLE target AS SELECT federal_project_recordid, nullif(purpose ->> 'metaid', '')::int8 AS "metaid", nullif(purpose ->> 'recordid', '')::text AS "recordid", nullif(purpose ->> 'startdate', '')::date AS "startdate", nullif(purpose ->> 'enddate', '')::date AS "enddate", nullif(purpose ->> 'name', '')::text AS "name", nullif(purpose ->> 'recordupdate', '')::timestamp AS "recordupdate" FROM ( SELECT nullif(body ->> 'recordid', '')::int AS federal_project_recordid, json_array_elements((body -> 'purposes')::json) as purpose FROM ext__federalproject WHERE json_array_length(body -> 'purposes') > 0 ) AS ps DISTRIBUTED BY (federal_project_recordid, recordid); COMMENT ON COLUMN target.metaid IS 'Идентификатор всех версий цели федерального проекта'; COMMENT ON COLUMN target.recordid IS 'Идентификатор актуальной версии цели федерального проекта'; COMMENT ON COLUMN target.startdate IS 'Дата начала действия записи'; COMMENT ON COLUMN target.enddate IS 'Дата окончания действия записи'; COMMENT ON COLUMN target.name IS 'Наименование цели федерального проекта'; COMMENT ON COLUMN target.recordupdate IS 'Дата обновления записи'; -- ProjectsFederalEntryMark -- DROP TABLE mark; CREATE TABLE mark AS SELECT federal_project_recordid, purpose ->> 'recordid' AS purpose_recordid, nullif(mark ->> 'metaid', '')::text AS "metaid", nullif(mark ->> 'recordid', '')::text AS "recordid", nullif(mark ->> 'startdate', '')::text AS "startdate", nullif(mark ->> 'enddate', '')::text AS "enddate", nullif(mark ->> 'mrkname', '')::text AS "mrkname", nullif(mark ->> 'typemrk', '')::text AS "typemrk", nullif(mark ->> 'okeirecordid', '')::text AS "okeirecordid", nullif(mark ->> 'okeicode', '')::text AS "okeicode", nullif(mark ->> 'okeiname', '')::text AS "okeiname", nullif(mark ->> 'basicvalueind', '')::numeric AS "basicvalueind", nullif(mark ->> 'setdate', '')::date AS "setdate", nullif(mark ->> 'value2018', '')::numeric AS "value2018", nullif(mark ->> 'value2019', '')::numeric AS "value2019", nullif(mark ->> 'value2020', '')::numeric AS "value2020", nullif(mark ->> 'value2021', '')::numeric AS "value2021", nullif(mark ->> 'value2022', '')::numeric AS "value2022", nullif(mark ->> 'value2023', '')::numeric AS "value2023", nullif(mark ->> 'value2024', '')::numeric AS "value2024", nullif(mark ->> 'recordupdate', '')::timestamp AS "recordupdate" FROM ( SELECT *, json_array_elements(purpose -> 'marks') as mark FROM ( SELECT nullif(body ->> 'recordid', '')::int AS federal_project_recordid, json_array_elements((body -> 'purposes')::json) as purpose FROM ext__federalproject WHERE json_array_length(body -> 'purposes') > 0 ) p ) m DISTRIBUTED BY (federal_project_recordid, purpose_recordid, recordid); COMMENT ON COLUMN mark.metaid IS 'Идентификатор всех версий показателя федерального проекта'; COMMENT ON COLUMN mark.recordid IS 'Идентификатор актуальной версии показателя федерального проекта'; COMMENT ON COLUMN mark.startdate IS 'Дата начала действия записи'; COMMENT ON COLUMN mark.enddate IS 'Дата окончания действия записи'; COMMENT ON COLUMN mark.mrkname IS 'Наименование показателя'; COMMENT ON COLUMN mark.typemrk IS 'Тип показателя'; COMMENT ON COLUMN mark.okeirecordid IS 'Идентификатор актуальной версии записи справочника «Единицы измерения»'; COMMENT ON COLUMN mark.okeicode IS 'Код единицы измерения'; COMMENT ON COLUMN mark.okeiname IS 'Наименование единицы измерения'; COMMENT ON COLUMN mark.basicvalueind IS 'Базовое значение показателя федерального проекта'; COMMENT ON COLUMN mark.setdate IS 'Дата расчета базового значения показателя федерального проекта'; COMMENT ON COLUMN mark.value2018 IS 'Значение показателя на 2018 год'; COMMENT ON COLUMN mark.value2019 IS 'Значение показателя на 2019 год'; COMMENT ON COLUMN mark.value2020 IS 'Значение показателя на 2020 год'; COMMENT ON COLUMN mark.value2021 IS 'Значение показателя на 2021 год'; COMMENT ON COLUMN mark.value2022 IS 'Значение показателя на 2022 год'; COMMENT ON COLUMN mark.value2023 IS 'Значение показателя на 2023 год'; COMMENT ON COLUMN mark.value2024 IS 'Значение показателя на 2024 год'; COMMENT ON COLUMN mark.recordupdate IS 'Дата обновления записи'; -- ProjectsFederalEntrySubjectMark -- DROP TABLE subject_mark; CREATE TABLE subject_mark AS SELECT federal_project_recordid, (purpose ->> 'recordid')::int8 AS purpose_recordid, nullif(mark ->> 'recordid', '')::int8 AS purpose_mark_recordid, nullif(subjectmark ->> 'okeirecordid', '')::int8 AS "okeirecordid", nullif(subjectmark ->> 'okeicode', '')::char(3) AS "okeicode", nullif(subjectmark ->> 'okeiname', '')::text AS "okeiname", nullif(subjectmark ->> 'basicvalueind', '')::text AS "basicvalueind", nullif(subjectmark ->> 'setdate', '')::date AS "setdate", nullif(subjectmark ->> 'value2018', '')::numeric AS "value2018", nullif(subjectmark ->> 'value2019', '')::numeric AS "value2019", nullif(subjectmark ->> 'value2020', '')::numeric AS "value2020", nullif(subjectmark ->> 'value2021', '')::numeric AS "value2021", nullif(subjectmark ->> 'value2022', '')::numeric AS "value2022", nullif(subjectmark ->> 'value2023', '')::numeric AS "value2023", nullif(subjectmark ->> 'value2024', '')::numeric AS "value2024", nullif(subjectmark ->> 'recordupdate', '')::timestamp AS "recordupdate", nullif(subjectmark ->> 'subjectcode', '')::int2 AS "subjectcode", nullif(subjectmark ->> 'subjectname', '')::text AS "subjectname" FROM ( SELECT *, json_array_elements(mark -> 'subjectmarks') as subjectmark FROM ( SELECT *, json_array_elements(purpose -> 'marks') as mark FROM ( SELECT nullif(body ->> 'recordid', '')::int AS federal_project_recordid, json_array_elements((body -> 'purposes')::json) as purpose FROM ext__federalproject WHERE json_array_length(body -> 'purposes') > 0 ) p ) m ) t DISTRIBUTED BY (federal_project_recordid, purpose_recordid, purpose_mark_recordid); COMMENT ON COLUMN subject_mark.subjectcode IS 'Код субъекта'; COMMENT ON COLUMN subject_mark.subjectname IS 'Наименование субъекта'; COMMENT ON COLUMN subject_mark.okeirecordid IS 'Идентификатор актуальной версии записи справочника «Единицы измерения»'; COMMENT ON COLUMN subject_mark.okeicode IS 'Код единицы измерения'; COMMENT ON COLUMN subject_mark.okeiname IS 'Наименование единицы измерения'; COMMENT ON COLUMN subject_mark.basicvalueind IS 'Базовое значение показателя по субъектам федерального проекта'; COMMENT ON COLUMN subject_mark.setdate IS 'Дата расчета базового значения показателя по субъектам федерального проекта'; COMMENT ON COLUMN subject_mark.value2018 IS 'Значение показателя на 2018 год'; COMMENT ON COLUMN subject_mark.value2019 IS 'Значение показателя на 2019 год'; COMMENT ON COLUMN subject_mark.value2020 IS 'Значение показателя на 2020 год'; COMMENT ON COLUMN subject_mark.value2021 IS 'Значение показателя на 2021 год'; COMMENT ON COLUMN subject_mark.value2022 IS 'Значение показателя на 2022 год'; COMMENT ON COLUMN subject_mark.value2023 IS 'Значение показателя на 2023 год'; COMMENT ON COLUMN subject_mark.value2024 IS 'Значение показателя на 2024 год'; COMMENT ON COLUMN subject_mark.recordupdate IS 'Дата обновления записи'; -- ProjectsFederalEntryMark -- DROP TABLE entry_mark; CREATE TABLE entry_mark AS SELECT federal_project_recordid, nullif(targetmark ->> 'recordid', '')::int8 AS "recordid", nullif(targetmark ->> 'metaid', '')::int8 AS "metaid", nullif(targetmark ->> 'startdate', '')::date AS "startdate", nullif(targetmark ->> 'enddate', '')::text AS "enddate", nullif(targetmark ->> 'mrkname', '')::text AS "mrkname", nullif(targetmark ->> 'typemrk', '')::text AS "typemrk", nullif(targetmark ->> 'okeirecordid', '')::int8 AS "okeirecordid", nullif(targetmark ->> 'okeicode', '')::char(3) AS "okeicode", nullif(targetmark ->> 'okeiname', '')::text AS "okeiname", nullif(targetmark ->> 'basicvalueind', '')::numeric AS "basicvalueind", nullif(targetmark ->> 'setdate', '')::date AS "setdate", nullif(targetmark ->> 'value2018', '')::numeric AS "value2018", nullif(targetmark ->> 'value2019', '')::numeric AS "value2019", nullif(targetmark ->> 'value2020', '')::numeric AS "value2020", nullif(targetmark ->> 'value2021', '')::numeric AS "value2021", nullif(targetmark ->> 'value2022', '')::numeric AS "value2022", nullif(targetmark ->> 'value2023', '')::numeric AS "value2023", nullif(targetmark ->> 'value2024', '')::numeric AS "value2024", nullif(targetmark ->> 'recordupdate', '')::text AS "recordupdate" FROM ( SELECT nullif(body ->> 'recordid', '')::int AS federal_project_recordid, json_array_elements((body -> 'targetmarks')::json) as targetmark FROM ext__federalproject WHERE json_array_length(body -> 'targetmarks') > 0 ) AS ps DISTRIBUTED BY (federal_project_recordid, recordid); COMMENT ON COLUMN entry_mark.metaid IS 'Идентификатор всех версий показателя федерального проекта'; COMMENT ON COLUMN entry_mark.recordid IS 'Идентификатор актуальной версии показателя федерального проекта'; COMMENT ON COLUMN entry_mark.startdate IS 'Дата начала действия записи'; COMMENT ON COLUMN entry_mark.enddate IS 'Дата окончания действия записи'; COMMENT ON COLUMN entry_mark.mrkname IS 'Наименование показателя'; COMMENT ON COLUMN entry_mark.typemrk IS 'Тип показателя'; COMMENT ON COLUMN entry_mark.okeirecordid IS 'Идентификатор актуальной версии записи справочника «Единицы измерения»'; COMMENT ON COLUMN entry_mark.okeicode IS 'Код единицы измерения'; COMMENT ON COLUMN entry_mark.okeiname IS 'Наименование единицы измерения'; COMMENT ON COLUMN entry_mark.basicvalueind IS 'Базовое значение показателя федерального проекта'; COMMENT ON COLUMN entry_mark.setdate IS 'Дата расчета базового значения показателя федерального проекта'; COMMENT ON COLUMN entry_mark.value2018 IS 'Значение показателя на 2018 год'; COMMENT ON COLUMN entry_mark.value2019 IS 'Значение показателя на 2019 год'; COMMENT ON COLUMN entry_mark.value2020 IS 'Значение показателя на 2020 год'; COMMENT ON COLUMN entry_mark.value2021 IS 'Значение показателя на 2021 год'; COMMENT ON COLUMN entry_mark.value2022 IS 'Значение показателя на 2022 год'; COMMENT ON COLUMN entry_mark.value2023 IS 'Значение показателя на 2023 год'; COMMENT ON COLUMN entry_mark.value2024 IS 'Значение показателя на 2024 год'; COMMENT ON COLUMN entry_mark.recordupdate IS 'Дата обновления записи'; -- ProjectsFederalEntrySubjectMark -- DROP TABLE subject_mark; CREATE TABLE subject_mark_ AS SELECT federal_project_recordid, (targetmark ->> 'recordid')::int8 AS targetmarks_recordid, nullif(mark ->> 'okeirecordid', '')::int8 AS "okeirecordid", nullif(mark ->> 'okeicode', '')::char(3) AS "okeicode", nullif(mark ->> 'okeiname', '')::text AS "okeiname", nullif(mark ->> 'basicvalueind', '')::text AS "basicvalueind", nullif(mark ->> 'setdate', '')::date AS "setdate", nullif(mark ->> 'value2018', '')::numeric AS "value2018", nullif(mark ->> 'value2019', '')::numeric AS "value2019", nullif(mark ->> 'value2020', '')::numeric AS "value2020", nullif(mark ->> 'value2021', '')::numeric AS "value2021", nullif(mark ->> 'value2022', '')::numeric AS "value2022", nullif(mark ->> 'value2023', '')::numeric AS "value2023", nullif(mark ->> 'value2024', '')::numeric AS "value2024", nullif(mark ->> 'recordupdate', '')::timestamp AS "recordupdate", nullif(mark ->> 'subjectcode', '')::int AS "subjectcode", nullif(mark ->> 'subjectname', '')::text AS "subjectname" FROM ( SELECT *, json_array_elements(targetmark -> 'subjectmarks') as mark FROM ( SELECT nullif(body ->> 'recordid', '')::int AS federal_project_recordid, json_array_elements((body -> 'targetmarks')::json) as targetmark FROM ext__federalproject WHERE json_array_length(body -> 'targetmarks') > 0 ) p ) m DISTRIBUTED BY (federal_project_recordid, targetmarks_recordid); COMMENT ON COLUMN subject_mark_.subjectcode IS 'Код субъекта'; COMMENT ON COLUMN subject_mark_.subjectname IS 'Наименование субъекта'; COMMENT ON COLUMN subject_mark_.okeirecordid IS 'Идентификатор актуальной версии записи справочника «Единицы измерения»'; COMMENT ON COLUMN subject_mark_.okeicode IS 'Код единицы измерения'; COMMENT ON COLUMN subject_mark_.okeiname IS 'Наименование единицы измерения'; COMMENT ON COLUMN subject_mark_.basicvalueind IS 'Базовое значение показателя по субъектам федерального проекта'; COMMENT ON COLUMN subject_mark_.setdate IS 'Дата расчета базового значения показателя по субъектам федерального проекта'; COMMENT ON COLUMN subject_mark_.value2018 IS 'Значение показателя на 2018 год'; COMMENT ON COLUMN subject_mark_.value2019 IS 'Значение показателя на 2019 год'; COMMENT ON COLUMN subject_mark_.value2020 IS 'Значение показателя на 2020 год'; COMMENT ON COLUMN subject_mark_.value2021 IS 'Значение показателя на 2021 год'; COMMENT ON COLUMN subject_mark_.value2022 IS 'Значение показателя на 2022 год'; COMMENT ON COLUMN subject_mark_.value2023 IS 'Значение показателя на 2023 год'; COMMENT ON COLUMN subject_mark_.value2024 IS 'Значение показателя на 2024 год'; COMMENT ON COLUMN subject_mark_.recordupdate IS 'Дата обновления записи'; -- ProjectsFederalEntryParticipant (participant) -- DROP TABLE participant; CREATE TABLE participant AS SELECT federal_project_recordid, nullif(participant ->> 'recordid', '')::int8 AS "recordid", nullif(participant ->> 'metaid', '')::int8 AS "metaid", nullif(participant ->> 'startdate', '')::timestamp AS "startdate", nullif(participant ->> 'enddate', '')::timestamp AS "enddate", nullif(participant ->> 'fio', '')::text AS "fio", nullif(participant ->> 'headpost', '')::text AS "headpost", nullif(participant ->> 'immsupervisor', '')::text AS "immsupervisor", nullif(participant ->> 'percemploy', '')::numeric AS "percemploy", nullif(participant ->> 'roleinproj', '')::text AS "roleinproj", nullif(participant ->> 'recordupdate', '')::timestamp AS "recordupdate" FROM ( SELECT nullif(body ->> 'recordid', '')::int AS federal_project_recordid, json_array_elements((body -> 'participants')::json) as participant FROM ext__federalproject ) AS ps DISTRIBUTED BY (federal_project_recordid, recordid); COMMENT ON COLUMN participant.metaid IS 'Идентификатор всех версий записи реестра Участники федерального проекта'; COMMENT ON COLUMN participant.recordid IS 'Идентификатор актуальной версии записи реестра Участники федерального проекта'; COMMENT ON COLUMN participant.startdate IS 'Дата начала действия записи'; COMMENT ON COLUMN participant.enddate IS 'Дата окончания действия записи'; COMMENT ON COLUMN participant.fio IS 'Фамилия, инициалы'; COMMENT ON COLUMN participant.headpost IS 'Должность'; COMMENT ON COLUMN participant.immsupervisor IS 'Непосредственный руководитель'; COMMENT ON COLUMN participant.percemploy IS 'Занятость в проекте (процентов)'; COMMENT ON COLUMN participant.roleinproj IS 'Роль в федеральном проекте'; COMMENT ON COLUMN participant.recordupdate IS 'Дата обновления записи'; -- ProjectsFederalEntryParticipantResult -- DROP TABLE participant_result; CREATE TABLE participant_result AS SELECT federal_project_recordid, participant ->> 'recordid' AS participant_recordid, nullif(result ->> 'recordid', '')::int8 AS "recordid", nullif(result ->> 'code', '')::char(2) AS "code", nullif(result ->> 'name', '')::text AS "name" FROM ( SELECT *, json_array_elements(participant -> 'results') as result FROM ( SELECT nullif(body ->> 'recordid', '')::int AS federal_project_recordid, json_array_elements((body -> 'participants')::json) as participant FROM ext__federalproject ) p WHERE json_array_length(participant -> 'results') > 0 ) m DISTRIBUTED BY (federal_project_recordid, participant_recordid, recordid); COMMENT ON COLUMN participant_result.recordid IS 'Идентификатор актуальной версии результата федерального проекта'; COMMENT ON COLUMN participant_result.code IS 'Код результата федерального проекта'; COMMENT ON COLUMN participant_result.name IS 'Наименование результата федерального проекта'; -- ProjectsFederalEntryTask (task) -- DROP TABLE task CREATE TABLE task AS SELECT federal_project_recordid, nullif(task ->> 'metaid', '')::int8 AS "metaid", nullif(task ->> 'recordid', '')::int8 AS "recordid", nullif(task ->> 'startdate', '')::date AS "startdate", nullif(task ->> 'enddate', '')::text AS "enddate", nullif(task ->> 'name', '')::text AS "name", nullif(task ->> 'code', '')::char(2) AS "code", nullif(task ->> 'recordupdate', '')::timestamp AS "recordupdate", nullif(task ->> 'nprecordid', '')::int8 AS "nprecordid" FROM ( SELECT nullif(body ->> 'recordid', '')::int AS federal_project_recordid, json_array_elements(body -> 'tasks') as task FROM ext__federalproject WHERE json_array_length(body -> 'tasks') > 0 ) AS t DISTRIBUTED BY (federal_project_recordid, recordid); COMMENT ON COLUMN task.metaid IS 'Идентификатор всех версий задач федерального проекта'; COMMENT ON COLUMN task.recordid IS 'Идентификатор задачи федерального проекта'; COMMENT ON COLUMN task.startdate IS 'Дата начала действия записи'; COMMENT ON COLUMN task.enddate IS 'Дата окончания действия записи'; COMMENT ON COLUMN task.name IS 'Наименование задачи'; COMMENT ON COLUMN task.code IS 'Код задачи'; COMMENT ON COLUMN task.recordupdate IS 'Дата обновления записи'; COMMENT ON COLUMN task.nprecordid IS 'Идентификатор актуальной версии задачи национального проекта'; -- ProjectsFederalEntryResult -- DROP TABLE result; CREATE TABLE result AS SELECT federal_project_recordid, nullif(task ->> 'recordid', '')::int8 AS "task_recordid", nullif(result ->> 'metaid', '')::int8 AS "metaid", nullif(result ->> 'recordid', '')::int8 AS "recordid", nullif(result ->> 'startdate', '')::date AS "startdate", nullif(result ->> 'enddate', '')::date AS "enddate", nullif(result ->> 'code', '')::char(2) AS "code", nullif(result ->> 'name', '')::text AS "name", nullif(result ->> 'respexec', '')::text AS "respexec", nullif(result ->> 'numbercharact', '')::text AS "numbercharact", nullif(result ->> 'recordupdate', '')::timestamp AS "recordupdate", nullif(result ->> 'typeres', '')::text AS "typeres", nullif(result ->> 'costwaycode', '')::text AS "costwaycode", nullif(result ->> 'directionexpenses', '')::text AS "directionexpenses", nullif(result ->> 'direxpcoderesf', '')::text AS "direxpcoderesf", nullif(result ->> 'direxpnameresf', '')::text AS "direxpnameresf" FROM ( SELECT *, json_array_elements(task -> 'results') as result FROM ( SELECT nullif(body ->> 'recordid', '')::int AS federal_project_recordid, json_array_elements(body -> 'tasks') as task FROM ext__federalproject ) AS t ) r DISTRIBUTED BY (federal_project_recordid, task_recordid, recordid); COMMENT ON COLUMN result.metaid IS 'Идентификатор всех версий результата федерального проекта'; COMMENT ON COLUMN result.recordid IS 'Идентификатор актуальной версии результата федерального проекта'; COMMENT ON COLUMN result.startdate IS 'Дата начала действия записи'; COMMENT ON COLUMN result.enddate IS 'Дата окончания действия записи'; COMMENT ON COLUMN result.code IS 'Код результата федерального проекта'; COMMENT ON COLUMN result.name IS 'Наименование результата федерального проекта'; COMMENT ON COLUMN result.respexec IS 'Ответственный исполнитель'; COMMENT ON COLUMN result.numbercharact IS 'Характеристика результата федерального проекта'; COMMENT ON COLUMN result.recordupdate IS 'Дата обновления записи'; -- ProjectsFederalEntryResult (finsupport) -- DROP TABLE finsupport CREATE TABLE finsupport AS SELECT federal_project_recordid, nullif(task ->> 'recordid', '')::int8 AS "task_recordid", nullif(result ->> 'recordid', '')::int8 AS "result_recordid", nullif(finsupport ->> 'metaid', '')::int8 AS "metaid", -- 'Идентификатор всех версий записи реестра «Финансовое обеспечение федеральных проектов»'; nullif(finsupport ->> 'recordid', '')::int8 AS "recordid", -- 'Идентификатор актуальной версии записи реестра «Финансовое обеспечение федеральных проектов»'; nullif(finsupport ->> 'startdate', '')::date AS "startdate", -- 'Дата начала действия записи'; nullif(finsupport ->> 'enddate', '')::date AS "enddate", -- 'Дата окончания действия записи'; nullif(finsupport ->> 'fo2018', '')::numeric AS "fo2018", -- 'Финансовое обеспечение - паспорт за 2018 год'; nullif(finsupport ->> 'fo2019', '')::numeric AS "fo2019", -- 'Финансовое обеспечение - паспорт за 2019 год'; nullif(finsupport ->> 'fo2020', '')::numeric AS "fo2020", -- 'Финансовое обеспечение - паспорт за 2020 год'; nullif(finsupport ->> 'fo2021', '')::numeric AS "fo2021", -- 'Финансовое обеспечение - паспорт за 2021 год'; nullif(finsupport ->> 'fo2022', '')::numeric AS "fo2022", -- 'Финансовое обеспечение - паспорт за 2022 год'; nullif(finsupport ->> 'fo2023', '')::numeric AS "fo2023", -- 'Финансовое обеспечение - паспорт за 2023 год'; nullif(finsupport ->> 'fo2024', '')::numeric AS "fo2024", -- 'Финансовое обеспечение - паспорт за 2024 год'; nullif(finsupport ->> 'finsource', '')::text AS "finsource", -- 'Источник финансирования'; nullif(finsupport ->> 'recordupdate', '')::timestamp AS "recordupdate" -- 'Дата обновления записи'; FROM ( SELECT *, json_array_elements(result -> 'finsupports') AS finsupport FROM ( SELECT *, json_array_elements(task -> 'results') as result FROM ( SELECT nullif(body ->> 'recordid', '')::int AS federal_project_recordid, json_array_elements(body -> 'tasks') as task FROM ext__federalproject ) AS t ) r ) f DISTRIBUTED BY (federal_project_recordid, task_recordid, result_recordid, recordid); COMMENT ON COLUMN finsupport.metaid IS 'Идентификатор всех версий записи реестра «Финансовое обеспечение федеральных проектов»'; COMMENT ON COLUMN finsupport.recordid IS 'Идентификатор актуальной версии записи реестра «Финансовое обеспечение федеральных проектов»'; COMMENT ON COLUMN finsupport.startdate IS 'Дата начала действия записи'; COMMENT ON COLUMN finsupport.enddate IS 'Дата окончания действия записи'; COMMENT ON COLUMN finsupport.fo2018 IS 'Финансовое обеспечение - паспорт за 2018 год'; COMMENT ON COLUMN finsupport.fo2019 IS 'Финансовое обеспечение - паспорт за 2019 год'; COMMENT ON COLUMN finsupport.fo2020 IS 'Финансовое обеспечение - паспорт за 2020 год'; COMMENT ON COLUMN finsupport.fo2021 IS 'Финансовое обеспечение - паспорт за 2021 год'; COMMENT ON COLUMN finsupport.fo2022 IS 'Финансовое обеспечение - паспорт за 2022 год'; COMMENT ON COLUMN finsupport.fo2023 IS 'Финансовое обеспечение - паспорт за 2023 год'; COMMENT ON COLUMN finsupport.fo2024 IS 'Финансовое обеспечение - паспорт за 2024 год'; COMMENT ON COLUMN finsupport.finsource IS 'Источник финансирования'; COMMENT ON COLUMN finsupport.recordupdate IS 'Дата обновления записи'; -- ProjectsFederalEntryResult -- DROP TABLE indicator; CREATE TABLE indicator AS SELECT federal_project_recordid, nullif(task ->> 'recordid', '')::int8 AS "task_recordid", nullif(result ->> 'recordid', '')::int8 AS "result_recordid", nullif(indicator ->> 'metaid', '')::int8 AS "metaid", nullif(indicator ->> 'recordid', '')::int8 AS "recordid", nullif(indicator ->> 'startdate', '')::date AS "startdate", nullif(indicator ->> 'enddate', '')::date AS "enddate", nullif(indicator ->> 'value', '')::numeric AS "value", nullif(indicator ->> 'enddateresult', '')::date AS "enddateresult", nullif(indicator ->> 'okeirecordid', '')::text AS "okeirecordid", nullif(indicator ->> 'okeicode', '')::char(3) AS "okeicode", nullif(indicator ->> 'okeiname', '')::text AS "okeiname", nullif(indicator ->> 'recordupdate', '')::timestamp AS "recordupdate" FROM ( SELECT *, json_array_elements(result -> 'indicators') AS indicator FROM ( SELECT *, json_array_elements(task -> 'results') as result FROM ( SELECT nullif(body ->> 'recordid', '')::int AS federal_project_recordid, json_array_elements(body -> 'tasks') as task FROM ext__federalproject ) AS t ) r ) f DISTRIBUTED BY (federal_project_recordid, task_recordid, result_recordid, recordid); COMMENT ON COLUMN indicator.metaid IS 'Идентификатор всех версий значения результата'; COMMENT ON COLUMN indicator.recordid IS 'Идентификатор актуальной версии значения результата'; COMMENT ON COLUMN indicator.startdate IS 'Дата начала действия записи'; COMMENT ON COLUMN indicator.enddate IS 'Дата окончания действия записи'; COMMENT ON COLUMN indicator.value IS 'Значение'; COMMENT ON COLUMN indicator.enddateresult IS 'Дата достижения'; COMMENT ON COLUMN indicator.okeirecordid IS 'Идентификатор актуальной версии записи справочника «Единицы измерения»'; COMMENT ON COLUMN indicator.okeicode IS 'Код единицы измерения'; COMMENT ON COLUMN indicator.okeiname IS 'Наименование единицы измерения'; COMMENT ON COLUMN indicator.recordupdate IS 'Дата обновления записи'; -- ProjectsFederalEntryResult -- DROP TABLE control_point; CREATE TABLE control_point AS SELECT federal_project_recordid, nullif(task ->> 'recordid', '')::int8 AS "task_recordid", nullif(result ->> 'recordid', '')::int8 AS "result_recordid", nullif(indicator ->> 'recordid', '')::int8 AS "indicator_recordid", nullif(controlpoint ->> 'recordid', '')::int8 AS "recordid", nullif(controlpoint ->> 'metaid', '')::int8 AS "metaid", nullif(controlpoint ->> 'startdate', '')::date AS "startdate", nullif(controlpoint ->> 'enddate', '')::date AS "enddate", nullif(controlpoint ->> 'code', '')::text AS "code", nullif(controlpoint ->> 'name', '')::text AS "name", nullif(controlpoint ->> 'enddateresult', '')::date AS "enddateresult", nullif(controlpoint ->> 'respexec', '')::text AS "respexec", nullif(controlpoint ->> 'levelcontrol', '')::text AS "levelcontrol", nullif(controlpoint ->> 'dockindid', '')::int8 AS "dockindid", nullif(controlpoint ->> 'dockindname', '')::text AS "dockindname", nullif(controlpoint ->> 'recordupdate', '')::timestamp AS "recordupdate" FROM ( SELECT *, json_array_elements(indicator -> 'controlpoints') AS controlpoint FROM ( SELECT *, json_array_elements(result -> 'indicators') AS indicator FROM ( SELECT *, json_array_elements(task -> 'results') as result FROM ( SELECT nullif(body ->> 'recordid', '')::int AS federal_project_recordid, json_array_elements(body -> 'tasks') as task FROM ext__federalproject ) AS t ) r ) f ) x DISTRIBUTED BY (federal_project_recordid, task_recordid, result_recordid, indicator_recordid, recordid); COMMENT ON COLUMN control_point.metaid IS 'Идентификатор всех версий контрольной точки'; COMMENT ON COLUMN control_point.recordid IS 'Идентификатор актуальной версии контрольной точки'; COMMENT ON COLUMN control_point.startdate IS 'Дата начала действия записи'; COMMENT ON COLUMN control_point.enddate IS 'Дата окончания действия записи'; COMMENT ON COLUMN control_point.code IS 'Код контрольной точки'; COMMENT ON COLUMN control_point.name IS 'Наименование контрольной точки'; COMMENT ON COLUMN control_point.enddateresult IS 'Дата достижения контрольной точки'; COMMENT ON COLUMN control_point.respexec IS 'Ответственный исполнитель за контрольную точку'; COMMENT ON COLUMN control_point.levelcontrol IS 'Уровень контроля контрольной точки'; COMMENT ON COLUMN control_point.dockindid IS 'Идентификатор актуальной версии записи справочника «Вид документа»'; COMMENT ON COLUMN control_point.dockindname IS 'Наименование вида документа'; COMMENT ON COLUMN control_point.recordupdate IS 'Дата обновления записи'; -- ProjectsFederalEntryResult -- DROP TABLE event; CREATE TABLE event AS SELECT federal_project_recordid, nullif(task ->> 'recordid', '')::int8 AS "task_recordid", nullif(result ->> 'recordid', '')::int8 AS "result_recordid", nullif(indicator ->> 'recordid', '')::int8 AS "indicator_recordid", nullif(controlpoint ->> 'recordid', '')::int8 AS "controlpoint_recordid", nullif(event ->> 'recordid', '')::int8 AS "recordid", nullif(event ->> 'metaid', '')::int8 AS "metaid", nullif(event ->> 'startdate', '')::date AS "startdate", nullif(event ->> 'enddate', '')::date AS "enddate", nullif(event ->> 'code', '')::char(2) AS "code", nullif(event ->> 'name', '')::text AS "name", nullif(event ->> 'startdateevent', '')::date AS "startdateevent", nullif(event ->> 'nddateevent', '')::date AS "nddateevent", nullif(event ->> 'respexec', '')::text AS "respexec", nullif(event ->> 'levelcontrol', '')::text AS "levelcontrol", nullif(event ->> 'dockindid', '')::text AS "dockindid", nullif(event ->> 'dockindname', '')::text AS "dockindname", nullif(event ->> 'characteristics', '')::text AS "characteristics", nullif(event ->> 'recordupdate', '')::timestamp AS "recordupdate" FROM ( SELECT *, json_array_elements(controlpoint -> 'events') AS event FROM ( SELECT *, json_array_elements(indicator -> 'controlpoints') AS controlpoint FROM ( SELECT *, json_array_elements(result -> 'indicators') AS indicator FROM ( SELECT *, json_array_elements(task -> 'results') as result FROM ( SELECT nullif(body ->> 'recordid', '')::int AS federal_project_recordid, json_array_elements(body -> 'tasks') as task FROM ext__federalproject ) AS t ) r ) f ) x ) e DISTRIBUTED BY (federal_project_recordid, task_recordid, result_recordid, indicator_recordid, controlpoint_recordid, recordid); COMMENT ON COLUMN event.metaid IS 'Идентификатор всех версий мероприятия'; COMMENT ON COLUMN event.recordid IS 'Идентификатор актуальной версии мероприятия'; COMMENT ON COLUMN event.startdate IS 'Дата начала действия записи'; COMMENT ON COLUMN event.enddate IS 'Дата окончания действия записи'; COMMENT ON COLUMN event.code IS 'Код мероприятия контрольной точки'; COMMENT ON COLUMN event.name IS 'Наименование мероприятия контрольной точки'; COMMENT ON COLUMN event.startdateevent IS 'Дата начала реализации мероприятия'; COMMENT ON COLUMN event.nddateevent IS 'Дата окончания реализации мероприятия'; COMMENT ON COLUMN event.respexec IS 'Ответственный исполнитель'; COMMENT ON COLUMN event.levelcontrol IS 'Уровень контроля контрольной точки'; COMMENT ON COLUMN event.dockindid IS 'Идентификатор актуальной версии записи справочника «Вид документа»'; COMMENT ON COLUMN event.dockindname IS 'Наименование вида документа'; COMMENT ON COLUMN event.characteristics IS 'Характеристика мероприятия контрольной точки '; COMMENT ON COLUMN event.recordupdate IS 'Дата обновления записи';