CREATE VIEW "geography_columns" AS SELECT current_database() AS f_table_catalog, n.nspname AS f_table_schema, c.relname AS f_table_name, a.attname AS f_geography_column, postgis_typmod_dims(a.atttypmod) AS coord_dimension, postgis_typmod_srid(a.atttypmod) AS srid, postgis_typmod_type(a.atttypmod) AS type FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n WHERE t.typname = 'geography'::name AND a.attisdropped = false AND a.atttypid = t.oid AND a.attrelid = c.oid AND c.relnamespace = n.oid AND (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'm'::"char", 'f'::"char", 'p'::"char"])) AND NOT pg_is_other_temp_schema(c.relnamespace) AND has_table_privilege(c.oid, 'SELECT'::text); ALTER TABLE "geography_columns" OWNER TO "postgres"; CREATE VIEW "geometry_columns" AS SELECT current_database()::character varying(256) AS f_table_catalog, n.nspname AS f_table_schema, c.relname AS f_table_name, a.attname AS f_geometry_column, COALESCE(postgis_typmod_dims(a.atttypmod), sn.ndims, 2) AS coord_dimension, COALESCE(NULLIF(postgis_typmod_srid(a.atttypmod), 0), sr.srid, 0) AS srid, replace(replace(COALESCE(NULLIF(upper(postgis_typmod_type(a.atttypmod)), 'GEOMETRY'::text), st.type, 'GEOMETRY'::text), 'ZM'::text, ''::text), 'Z'::text, ''::text)::character varying(30) AS type FROM pg_class c JOIN pg_attribute a ON a.attrelid = c.oid AND NOT a.attisdropped JOIN pg_namespace n ON c.relnamespace = n.oid JOIN pg_type t ON a.atttypid = t.oid LEFT JOIN ( SELECT s.connamespace, s.conrelid, s.conkey, replace(split_part(s.consrc, ''''::text, 2), ')'::text, ''::text) AS type FROM ( SELECT pg_constraint.connamespace, pg_constraint.conrelid, pg_constraint.conkey, pg_get_constraintdef(pg_constraint.oid) AS consrc FROM pg_constraint) s WHERE s.consrc ~~* '%geometrytype(% = %'::text) st ON st.connamespace = n.oid AND st.conrelid = c.oid AND (a.attnum = ANY (st.conkey)) LEFT JOIN ( SELECT s.connamespace, s.conrelid, s.conkey, replace(split_part(s.consrc, ' = '::text, 2), ')'::text, ''::text)::integer AS ndims FROM ( SELECT pg_constraint.connamespace, pg_constraint.conrelid, pg_constraint.conkey, pg_get_constraintdef(pg_constraint.oid) AS consrc FROM pg_constraint) s WHERE s.consrc ~~* '%ndims(% = %'::text) sn ON sn.connamespace = n.oid AND sn.conrelid = c.oid AND (a.attnum = ANY (sn.conkey)) LEFT JOIN ( SELECT s.connamespace, s.conrelid, s.conkey, replace(replace(split_part(s.consrc, ' = '::text, 2), ')'::text, ''::text), '('::text, ''::text)::integer AS srid FROM ( SELECT pg_constraint.connamespace, pg_constraint.conrelid, pg_constraint.conkey, pg_get_constraintdef(pg_constraint.oid) AS consrc FROM pg_constraint) s WHERE s.consrc ~~* '%srid(% = %'::text) sr ON sr.connamespace = n.oid AND sr.conrelid = c.oid AND (a.attnum = ANY (sr.conkey)) WHERE (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'm'::"char", 'f'::"char", 'p'::"char"])) AND NOT c.relname = 'raster_columns'::name AND t.typname = 'geometry'::name AND NOT pg_is_other_temp_schema(c.relnamespace) AND has_table_privilege(c.oid, 'SELECT'::text); ALTER TABLE "geometry_columns" OWNER TO "postgres"; CREATE RULE "geometry_columns_insert" AS ON INSERT TO "geometry_columns" DO INSTEAD NOTHING;; CREATE RULE "geometry_columns_update" AS ON UPDATE TO "geometry_columns" DO INSTEAD NOTHING;; CREATE RULE "geometry_columns_delete" AS ON DELETE TO "geometry_columns" DO INSTEAD NOTHING;; CREATE VIEW "industry_supervision_plan_view" AS SELECT industry_supervision_plan.id, industry_supervision_plan.type, industry_supervision_plan.unit, industry_supervision_plan.name, industry_supervision_plan.content, industry_supervision_plan.level, industry_supervision_plan.completion_time, industry_supervision_plan.scene, industry_supervision_plan.label, industry_supervision_plan.annex, industry_supervision_plan.release_time, industry_supervision_plan.state, industry_supervision_plan.order_num, industry_supervision_plan.create_time, industry_supervision_plan.create_user, industry_supervision_plan.update_time, industry_supervision_plan.update_user, industry_supervision_plan.del_flag, industry_supervision_plan.cb, industry_supervision_plan_lifeline.project_progress, industry_supervision_plan_lifeline.project_progress_description FROM industry_supervision_plan LEFT JOIN industry_supervision_plan_lifeline ON industry_supervision_plan.id::text = industry_supervision_plan_lifeline.id::text; ALTER TABLE "industry_supervision_plan_view" OWNER TO "postgres"; COMMENT ON VIEW "industry_supervision_plan_view" IS '建设规划视图'; CREATE VIEW "message_read_view" AS SELECT warning_info.warning_name AS name, NULL::text AS source_id2, message_read.id, message_read.source_id, message_read.user_id, message_read.source_type, message_read.deleted, message_read.create_time, message_read.update_time, message_read.creater_id, message_read.updater_id, message_read.is_read, message_read.remark FROM message_read LEFT JOIN warning_info ON warning_info.id::text = message_read.source_id::text WHERE message_read.source_type::text = '1'::text AND message_read.is_read = 0 AND message_read.deleted = 1 UNION ALL SELECT industry_supervision_report.title AS name, NULL::text AS source_id2, message_read.id, message_read.source_id, message_read.user_id, message_read.source_type, message_read.deleted, message_read.create_time, message_read.update_time, message_read.creater_id, message_read.updater_id, message_read.is_read, message_read.remark FROM message_read LEFT JOIN industry_supervision_report ON industry_supervision_report.id::text = message_read.source_id::text WHERE message_read.source_type::text = '2'::text AND message_read.is_read = 0 AND message_read.deleted = 1 UNION ALL SELECT work_order_admin.work_order_content AS name, work_order_admin.work_order_code AS source_id2, message_read.id, message_read.source_id, message_read.user_id, message_read.source_type, message_read.deleted, message_read.create_time, message_read.update_time, message_read.creater_id, message_read.updater_id, message_read.is_read, message_read.remark FROM message_read LEFT JOIN work_order_admin ON work_order_admin.id::text = message_read.source_id::text WHERE message_read.source_type::text = '3'::text AND message_read.is_read = 0 AND message_read.deleted = 1; ALTER TABLE "message_read_view" OWNER TO "postgres"; COMMENT ON VIEW "message_read_view" IS '通知查询语句'; CREATE VIEW "view_aqzxjcjl" AS SELECT rq_jg_aqzxjcjl.bsm, rq_jg_aqzxjcjl.jcrq, rq_jg_aqzxjcjl.czyh, '0'::text AS scene, rq_jg_aqzxjcjl.xzqhdm FROM rq_jg_aqzxjcjl; ALTER TABLE "view_aqzxjcjl" OWNER TO "postgres"; COMMENT ON VIEW "view_aqzxjcjl" IS '各个专项的安全专项检查记录'; CREATE VIEW "view_jcd_collect" AS SELECT 'RQ_JS_JCD'::text AS "table", '0'::text AS type, '燃气'::text AS code, rq_js_jcd.bsm, rq_js_jcd.xzqhdm, rq_js_jcd.dwmc, rq_js_jcd.ssmc, rq_js_jcd.ssbm, rq_js_jcd.jcwz, rq_js_jcd.gc, rq_js_jcd.gcjz, rq_js_jcd.bz, NULLIF(st_asgeojson(rq_js_jcd.kjxx), ''::text) AS geojson FROM rq_js_jcd UNION ALL SELECT 'PS_JS_JCD'::text AS "table", '1'::text AS type, '内涝'::text AS code, ps_js_jcd.bsm, ps_js_jcd.xzqhdm, ps_js_jcd.dwmc, ps_js_jcd.ssmc, ps_js_jcd.ssbm, ps_js_jcd.jcwz, ps_js_jcd.gc, ps_js_jcd.gcjz, ps_js_jcd.bz, NULLIF(st_asgeojson(ps_js_jcd.kjxx), ''::text) AS geojson FROM ps_js_jcd UNION ALL SELECT 'GS_JS_JCD'::text AS "table", '2'::text AS type, '供水'::text AS code, gs_js_jcd.bsm, gs_js_jcd.xzqhdm, gs_js_jcd.dwmc, gs_js_jcd.ssmc, gs_js_jcd.ssbm, gs_js_jcd.jcwz, gs_js_jcd.gc, gs_js_jcd.gcjz, gs_js_jcd.bz, NULLIF(st_asgeojson(gs_js_jcd.kjxx), ''::text) AS geojson FROM gs_js_jcd UNION ALL SELECT 'ql_js_jcd'::text AS "table", '5'::text AS type, '桥梁'::text AS code, ql_js_jcd.bsm, ql_js_jcd.xzqhdm, ql_js_jcd.dwmc, ql_js_jcd.ssmc, ql_js_jcd.ssbm, ql_js_jcd.jcwz, ql_js_jcd.gc, ql_js_jcd.gcjz, ql_js_jcd.bz, NULLIF(st_asgeojson(ql_js_jcd.kjxx), ''::text) AS geojson FROM ql_js_jcd UNION ALL SELECT 'sg_js_jcd'::text AS "table", '3'::text AS type, '第三方'::text AS code, sg_js_jcd.bsm, sg_js_jcd.xzqhdm, sg_js_jcd.dwmc, sg_js_jcd.ssmc, sg_js_jcd.ssbm, sg_js_jcd.jcwz, sg_js_jcd.gc, sg_js_jcd.gcjz, sg_js_jcd.bz, NULLIF(st_asgeojson(sg_js_jcd.kjxx), ''::text) AS geojson FROM sg_js_jcd; ALTER TABLE "view_jcd_collect" OWNER TO "postgres"; COMMENT ON VIEW "view_jcd_collect" IS '监测点统计(系统中已不适用了)'; CREATE VIEW "view_jcd_jcxyz" AS SELECT '供水'::text AS name, '2'::text AS scene, gs_jc_jcxyz.bsm, gs_jc_jcxyz.sbbsm, gs_jc_jcxyz.sblx, gs_jc_jcxyz.jczb, gs_jc_jcxyz.bjyz, gs_jc_jcxyz.bz, gs_jc_jcxyz.yskzjbz, gs_jc_jcxyz.sjtbzt, gs_jc_jcxyz.tbsj FROM gs_jc_jcxyz UNION ALL SELECT '排水'::text AS name, '1'::text AS scene, ps_jc_jcxyz.bsm, ps_jc_jcxyz.sbbsm, ps_jc_jcxyz.sblx, ps_jc_jcxyz.jczb, ps_jc_jcxyz.bjyz::character varying AS bjyz, ps_jc_jcxyz.bz, ps_jc_jcxyz.yskzjbz, ps_jc_jcxyz.sjtbzt, ps_jc_jcxyz.tbsj FROM ps_jc_jcxyz UNION ALL SELECT '施工'::text AS name, '3'::text AS scene, sg_jc_jcxyz.bsm, sg_jc_jcxyz.sbbsm, sg_jc_jcxyz.sblx, sg_jc_jcxyz.jczb, sg_jc_jcxyz.bjyz::character varying AS bjyz, sg_jc_jcxyz.bz, sg_jc_jcxyz.yskzjbz, sg_jc_jcxyz.sjtbzt, sg_jc_jcxyz.tbsj FROM sg_jc_jcxyz UNION ALL SELECT '燃气'::text AS name, '0'::text AS scene, rq_jc_jcxyz.bsm, rq_jc_jcxyz.sbbsm, rq_jc_jcxyz.sblx, rq_jc_jcxyz.jczb, rq_jc_jcxyz.bjyz::character varying AS bjyz, rq_jc_jcxyz.bz, NULL::character varying AS yskzjbz, NULL::character varying AS sjtbzt, rq_jc_jcxyz.tbsj FROM rq_jc_jcxyz UNION ALL SELECT '桥梁'::text AS name, '5'::text AS scene, ql_jc_jcxyz.bsm, ql_jc_jcxyz.sbbsm, ql_jc_jcxyz.sblx, ql_jc_jcxyz.jczb, ql_jc_jcxyz.bjyz::character varying AS bjyz, ql_jc_jcxyz.bz, ql_jc_jcxyz.yskzjbz, ql_jc_jcxyz.sjtbzt, ql_jc_jcxyz.tbsj FROM ql_jc_jcxyz; ALTER TABLE "view_jcd_jcxyz" OWNER TO "postgres"; COMMENT ON VIEW "view_jcd_jcxyz" IS '监测点_监测项阈值'; CREATE VIEW "view_lhjcjl" AS SELECT rq_jg_lhjcjl.bsm, rq_jg_lhjcjl.jcrq, '0'::text AS scene, rq_jg_lhjcjl.xzqhdm FROM rq_jg_lhjcjl; ALTER TABLE "view_lhjcjl" OWNER TO "postgres"; COMMENT ON VIEW "view_lhjcjl" IS '各个专项的安全专项检查记录'; CREATE VIEW "view_risk_collect" AS SELECT archives_risk.id, archives_risk."table", archives_risk.scene AS type, archives_risk.code, archives_risk.childtype, archives_risk.bsm, archives_risk.area AS xzqhdm, CASE archives_risk.level WHEN '1'::text THEN 1 WHEN '2'::text THEN 2 WHEN '3'::text THEN 3 WHEN '4'::text THEN 4 ELSE NULL::integer END AS fxdj, archives_risk.name AS fxmc, archives_risk.fxms, archives_risk.dispose_progress, archives_risk.dispose_progress AS gkzt, archives_risk.type AS fxlx, NULL::text AS is_often, archives_risk.pgsj, archives_risk.kjxx, archives_risk.tbsj, archives_risk.zrdw, archives_risk.pgdw, 60 AS pgfz FROM archives_risk WHERE archives_risk.del_flag = 1; ALTER TABLE "view_risk_collect" OWNER TO "postgres"; CREATE VIEW "view_zgjl" AS SELECT rq_jg_zgjl.bsm, rq_jg_zgjl.zgrq, '0'::text AS scene, rq_jg_zgjl.xzqhdm FROM rq_jg_zgjl; ALTER TABLE "view_zgjl" OWNER TO "postgres"; COMMENT ON VIEW "view_zgjl" IS '各个专项的整改记录属性结构表'; CREATE VIEW "warning_info_view" AS SELECT warning_info.id, warning_info.scene, warning_info.warning_name, warning_info.warning_source, warning_info.equipment_name, warning_info.equipment_number, warning_info.warning_type, warning_info.warning_site, warning_info.area, warning_info.warning_rank, warning_info.generated_time, warning_info.report_time, warning_info.warning_describe, warning_info.report, warning_info.analyses, warning_info.disposal_state, warning_info.disposal_user_name, warning_info.disposal_user_id, warning_info.disposal_time, warning_info.disposal_suggest, warning_info.disposal_complete_time, warning_info.disposal_report, warning_info.remark, warning_info.geom_data, warning_info.defined, warning_info.deleted, warning_info.created_at, warning_info.created_by, warning_info.updated_at, warning_info.updated_by, warning_info.analyses_report, warning_info.warning_state, warning_info.streaming_url, warning_info.warning_number, warning_info.timely_status, warning_info.table_name, warning_info.threshold_value, warning_info_lifeline.associated_plan_lifeline, warning_info_lifeline.warning_rank_lifeline, warning_info_lifeline.warning_type_lifeline FROM warning_info LEFT JOIN warning_info_lifeline ON warning_info_lifeline.id::text = warning_info.id::text WHERE warning_info.table_name::text <> 'rq_jc_jcbj'::text UNION ALL SELECT warning_info.id, warning_info.scene, warning_info.warning_name, warning_info.warning_source, warning_info.equipment_name, warning_info.equipment_number, warning_info.warning_type, warning_info.warning_site, warning_info.area, warning_info.warning_rank, warning_info.generated_time, warning_info.report_time, warning_info.warning_describe, warning_info.report, warning_info.analyses, warning_info.disposal_state, warning_info.disposal_user_name, warning_info.disposal_user_id, warning_info.disposal_time, warning_info.disposal_suggest, warning_info.disposal_complete_time, warning_info.disposal_report, warning_info.remark, warning_info.geom_data, warning_info.defined, warning_info.deleted, warning_info.created_at, warning_info.created_by, warning_info.updated_at, warning_info.updated_by, warning_info.analyses_report, warning_info.warning_state, warning_info.streaming_url, warning_info.warning_number, warning_info.timely_status, warning_info.table_name, warning_info.threshold_value, warning_info_lifeline.associated_plan_lifeline, warning_info_lifeline.warning_rank_lifeline, warning_info_lifeline.warning_type_lifeline FROM ( SELECT row_number() OVER (PARTITION BY warning_info_1.warning_number ORDER BY warning_info_1.report_time DESC) AS rownum, warning_info_1.id, warning_info_1.scene, warning_info_1.warning_name, warning_info_1.warning_source, warning_info_1.equipment_name, warning_info_1.equipment_number, warning_info_1.warning_type, warning_info_1.warning_site, warning_info_1.area, warning_info_1.warning_rank, warning_info_1.generated_time, warning_info_1.report_time, warning_info_1.warning_describe, warning_info_1.report, warning_info_1.analyses, warning_info_1.disposal_state, warning_info_1.disposal_user_name, warning_info_1.disposal_user_id, warning_info_1.disposal_time, warning_info_1.disposal_suggest, warning_info_1.disposal_complete_time, warning_info_1.disposal_report, warning_info_1.remark, warning_info_1.geom_data, warning_info_1.defined, warning_info_1.deleted, warning_info_1.created_at, warning_info_1.created_by, warning_info_1.updated_at, warning_info_1.updated_by, warning_info_1.analyses_report, warning_info_1.warning_state, warning_info_1.streaming_url, warning_info_1.table_name, warning_info_1.threshold_value, warning_info_1.warning_number, warning_info_1.timely_status FROM warning_info warning_info_1 WHERE warning_info_1.table_name::text = 'rq_jc_jcbj'::text) warning_info LEFT JOIN warning_info_lifeline ON warning_info_lifeline.id::text = warning_info.id::text WHERE warning_info.rownum = 1; ALTER TABLE "warning_info_view" OWNER TO "postgres"; COMMENT ON VIEW "warning_info_view" IS '关联warning_info_lifeline表的预警表';