Part 23 : VIEW PostgreSQL

Ex:1

 CREATE OR REPLACE 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;
GRANT ALL ON TABLE public.geography_columns TO postgres;
GRANT SELECT ON TABLE public.geography_columns TO public;

EX:2

CREATE OR REPLACE VIEW public.hmp_2017_view AS
 SELECT yr_2017.gid,
    yr_2017.latitude,
    yr_2017.longitude,
    yr_2017.geom,
    yr_2017.tempdate,
    yr_2017.year,
    yr_2017.month,
    yr_2017.day,
    yr_2017.maxt,
    round(
        CASE
            WHEN yr_2017.mint < 3::double precision THEN 0.1
            WHEN yr_2017.mint < 6::double precision THEN 0.2
            WHEN yr_2017.mint < 9::double precision THEN 0.3
            WHEN yr_2017.mint < 12::double precision THEN 0.4
            WHEN yr_2017.mint < 15::double precision THEN 0.5
            WHEN yr_2017.mint < 18::double precision THEN 0.6
            WHEN yr_2017.mint < 22::double precision THEN 0.7
            WHEN yr_2017.mint < 25::double precision THEN 0.8
            WHEN yr_2017.mint < 28::double precision THEN 0.9
            ELSE 1::numeric
        END, 2)::double precision AS mint, yr_2017.rain, yr_2017.windspeed,
       yr_2017.sourcetype, yr_2017.source,yr_2017.humiditymin,
       yr_2017.humiditymax, yr_2017.winddir   FROM yr_2017
      WHERE yr_2017.tempdate = '2017-10-10'::date;

ALTER TABLE public.hmp_2017_view
  OWNER TO postgres;

Ex:- 3

CREATE OR REPLACE VIEW public.weathermine_points AS
 SELECT a.gid,
    a.geom,
    a.sourcetype,
    a.source,
    a.state,
    a.district,
    a.tehsil,
        CASE
            WHEN a.sourcetype = 1 THEN 'Real'::text
            ELSE 'Interpolated'::text
        END AS datatype
   FROM locations a
  WHERE a.state IS NOT NULL;

Ex:4-

CREATE OR REPLACE VIEW public.raster_overviews AS
 SELECT current_database() AS o_table_catalog,n.nspname AS o_table_schema,
    c.relname AS o_table_name, a.attname AS o_raster_column,
    current_database() AS r_table_catalog,
    split_part(split_part(s.consrc, '''::name'::text, 1), ''''::text, 2)::name AS     r_table_schema,
    split_part(split_part(s.consrc, '''::name'::text, 2), ''''::text, 2)::name AS r_table_name,
    split_part(split_part(s.consrc, '''::name'::text, 3), ''''::text, 2)::name AS r_raster_column,
    btrim(split_part(s.consrc, ','::text, 2))::integer AS overview_factor
   FROM pg_class c,
    pg_attribute a,
    pg_type t,
    pg_namespace n,
    ( SELECT pg_constraint.connamespace,
            pg_constraint.conrelid,
            pg_constraint.conkey,
            pg_get_constraintdef(pg_constraint.oid) AS consrc
           FROM pg_constraint) s
 WHERE t.typname = 'raster'::name AND a.attisdropped = false 

AND a.atttypid = t.oid AND a.attrelid = c.oid AND 

c.relnamespace = n.oid AND (c.relkind::text = ANY (ARRAY['r'::character(1), 'v'::character(1), 'm'::character(1), 'f'::character(1)]::text[])) AND s.connamespace = n.oid AND s.conrelid = c.oid AND s.consrc ~~ '%_overview_constraint(%'::text AND NOT pg_is_other_temp_schema(c.relnamespace) AND has_table_privilege(c.oid, 'SELECT'::text);