programs_info/无锡市生命线项目/数据库备份/正式_public视图语句.sql

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表的预警表';