country_node_attributes_view

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

country_name

text

2147483647

√

null

level_name

text

2147483647

√

null

attribute_id

int4

√

null

attribute_name

text

2147483647

√

null

description

text

2147483647

√

null

year

int4

√

null

attribute_type

text

2147483647

√

null

Table contained 0 rows

View Definition

WITH level_2_node_ids AS (
         SELECT level_2.parent_id AS country_id,
            level_2.id AS region_id,
            2 AS level
           FROM (nodes country
             JOIN nodes level_2 ON ((level_2.parent_id = country.id)))
          WHERE (country.id IN ( SELECT country_levels.country_id
                   FROM country_levels))
        ), level_3_node_ids AS (
         SELECT level_2.country_id,
            level_3.id AS region_id,
            3 AS level
           FROM (level_2_node_ids level_2
             JOIN nodes level_3 ON ((level_3.parent_id = level_2.region_id)))
        ), level_4_node_ids AS (
         SELECT level_3.country_id,
            level_4.id AS region_id,
            4 AS level
           FROM (level_3_node_ids level_3
             JOIN nodes level_4 ON ((level_4.parent_id = level_3.region_id)))
        ), level_5_node_ids AS (
         SELECT level_4.country_id,
            level_5.id AS region_id,
            5 AS level
           FROM (level_4_node_ids level_4
             JOIN nodes level_5 ON ((level_5.parent_id = level_4.region_id)))
        ), level_6_node_ids AS (
         SELECT level_5.country_id,
            level_6.id AS region_id,
            6 AS level
           FROM (level_5_node_ids level_5
             JOIN nodes level_6 ON ((level_6.parent_id = level_5.region_id)))
        ), all_country_level_nodes AS (
         SELECT level_2_node_ids.country_id,
            level_2_node_ids.region_id,
            level_2_node_ids.level
           FROM level_2_node_ids
        UNION
         SELECT level_3_node_ids.country_id,
            level_3_node_ids.region_id,
            level_3_node_ids.level
           FROM level_3_node_ids
        UNION
         SELECT level_4_node_ids.country_id,
            level_4_node_ids.region_id,
            level_4_node_ids.level
           FROM level_4_node_ids
        UNION
         SELECT level_5_node_ids.country_id,
            level_5_node_ids.region_id,
            level_5_node_ids.level
           FROM level_5_node_ids
        UNION
         SELECT level_6_node_ids.country_id,
            level_6_node_ids.region_id,
            level_6_node_ids.level
           FROM level_6_node_ids
        )
 SELECT DISTINCT node_names.name AS country_name,
    country_levels.name AS level_name,
    quants.id AS attribute_id,
    quants.name AS attribute_name,
    quants.description,
    (date_part('year'::text, node_quants.time_start))::integer AS year,
    'Quant'::text AS attribute_type
   FROM ((((all_country_level_nodes
     JOIN country_levels ON ((all_country_level_nodes.country_id = country_levels.country_id)))
     JOIN node_names ON ((node_names.node_id = all_country_level_nodes.country_id)))
     JOIN node_quants ON ((node_quants.node_id = all_country_level_nodes.region_id)))
     JOIN quants ON ((quants.id = node_quants.quant_id)))
  WHERE (node_names.is_default AND (country_levels.level = all_country_level_nodes.level))
UNION
 SELECT DISTINCT node_names.name AS country_name,
    country_levels.name AS level_name,
    quals.id AS attribute_id,
    quals.name AS attribute_name,
    quals.description,
    (date_part('year'::text, node_quals.time_start))::integer AS year,
    'Qual'::text AS attribute_type
   FROM ((((all_country_level_nodes
     JOIN country_levels ON ((all_country_level_nodes.country_id = country_levels.country_id)))
     JOIN node_names ON ((node_names.node_id = all_country_level_nodes.country_id)))
     JOIN node_quals ON ((node_quals.node_id = all_country_level_nodes.region_id)))
     JOIN quals ON ((quals.id = node_quals.qual_id)))
  WHERE (node_names.is_default AND (country_levels.level = all_country_level_nodes.level))
UNION
 SELECT DISTINCT node_names.name AS country_name,
    country_levels.name AS level_name,
    inds.id AS attribute_id,
    inds.name AS attribute_name,
    inds.description,
    (date_part('year'::text, node_inds.time_start))::integer AS year,
    'Ind'::text AS attribute_type
   FROM ((((all_country_level_nodes
     JOIN country_levels ON ((all_country_level_nodes.country_id = country_levels.country_id)))
     JOIN node_names ON ((node_names.node_id = all_country_level_nodes.country_id)))
     JOIN node_inds ON ((node_inds.node_id = all_country_level_nodes.region_id)))
     JOIN inds ON ((inds.id = node_inds.ind_id)))
  WHERE (node_names.is_default AND (country_levels.level = all_country_level_nodes.level))
  ORDER BY 1, 2, 4, 6;

Columns

View Definition

Possibly Referenced Tables/Views