programs_info/无锡市生命线项目/数据库备份/删除前备份/政务网正式同步到政务网测试sql语句/view.sql

CREATE VIEW "public"."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 "public"."geography_columns" OWNER TO "postgres";





CREATE VIEW "public"."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 "public"."geometry_columns" OWNER TO "postgres";

CREATE RULE "geometry_columns_insert" AS ON INSERT TO "public"."geometry_columns" DO INSTEAD NOTHING;;

CREATE RULE "geometry_columns_update" AS ON UPDATE TO "public"."geometry_columns" DO INSTEAD NOTHING;;

CREATE RULE "geometry_columns_delete" AS ON DELETE TO "public"."geometry_columns" DO INSTEAD NOTHING;;




CREATE VIEW "public"."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 "public"."view_aqzxjcjl" OWNER TO "postgres";

COMMENT ON VIEW "public"."view_aqzxjcjl" IS '各个专项的安全专项检查记录';




CREATE VIEW "public"."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 "public"."view_jcd_collect" OWNER TO "postgres";

COMMENT ON VIEW "public"."view_jcd_collect" IS '监测点统计(系统中已不适用了)';



CREATE VIEW "public"."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 "public"."view_jcd_jcxyz" OWNER TO "postgres";

COMMENT ON VIEW "public"."view_jcd_jcxyz" IS '监测点_监测项阈值';



CREATE VIEW "public"."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 "public"."view_lhjcjl" OWNER TO "postgres";

COMMENT ON VIEW "public"."view_lhjcjl" IS '各个专项的安全专项检查记录';



CREATE VIEW "public"."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 "public"."view_risk_collect" OWNER TO "postgres";



CREATE VIEW "public"."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 "public"."view_zgjl" OWNER TO "postgres";

COMMENT ON VIEW "public"."view_zgjl" IS '各个专项的整改记录属性结构表';