releases_view

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

release_id

int4

√

null

context_id

int4

√

null

country_id

int8

√

null

version

text

2147483647

√

null

country

text

2147483647

√

null

com_id

int4

√

null

commodity

text

2147483647

√

null

ref_id

int4

√

null

title

text

2147483647

√

null

quality_control_passed

bool

√

null

years

_float8

17,17

√

null

columns

_text

2147483647

√

null

flow_quants

_text

2147483647

√

null

flow_quals

_text

2147483647

√

null

flow_inds

_text

2147483647

√

null

node_quants

_text

2147483647

√

null

node_quals

_text

2147483647

√

null

node_inds

_text

2147483647

√

null

Table contained 0 rows

View Definition

WITH ref_years AS (
         SELECT DISTINCT flows.ref_id,
            date_part('year'::text, flows.time_start) AS year
           FROM flows
        ), ref_node_roles AS (
         SELECT refs_1.id AS ref_id,
            get_reference_node_roles(refs_1.id) AS columns
           FROM refs refs_1
        ), ref_flow_quants AS (
         SELECT refs_1.id AS ref_id,
            get_reference_flow_quants(refs_1.id) AS flow_quants
           FROM refs refs_1
        ), ref_flow_quals AS (
         SELECT refs_1.id AS ref_id,
            get_reference_flow_quals(refs_1.id) AS flow_quals
           FROM refs refs_1
        ), ref_flow_inds AS (
         SELECT refs_1.id AS ref_id,
            get_reference_flow_inds(refs_1.id) AS flow_inds
           FROM refs refs_1
        )
 SELECT DISTINCT releases.id AS release_id,
    contexts.id AS context_id,
    refs.country_id,
    refs.version,
    node_names.name AS country,
    refs.com_id,
    commodity_names.name AS commodity,
    refs.id AS ref_id,
    refs.title,
    refs.quality_control_passed,
    array_agg(DISTINCT ref_years.year ORDER BY ref_years.year) AS years,
    ref_node_roles.columns,
    ref_flow_quants.flow_quants,
    ref_flow_quals.flow_quals,
    ref_flow_inds.flow_inds,
    array_agg(DISTINCT quants.name ORDER BY quants.name) AS node_quants,
    array_agg(DISTINCT quals.name ORDER BY quals.name) AS node_quals,
    array_agg(DISTINCT inds.name ORDER BY inds.name) AS node_inds
   FROM ((((((((((((((((releases
     FULL JOIN release_refs ON ((releases.id = release_refs.release_id)))
     FULL JOIN refs ON ((release_refs.ref_id = refs.id)))
     FULL JOIN contexts ON (((contexts.country_id = refs.country_id) AND (contexts.com_id = refs.com_id))))
     FULL JOIN commodity_names ON ((refs.com_id = commodity_names.com_id)))
     FULL JOIN node_names ON ((refs.country_id = node_names.node_id)))
     FULL JOIN ref_years ON ((refs.id = ref_years.ref_id)))
     FULL JOIN ref_node_roles ON ((refs.id = ref_node_roles.ref_id)))
     FULL JOIN ref_flow_quants ON ((refs.id = ref_flow_quants.ref_id)))
     FULL JOIN ref_flow_quals ON ((refs.id = ref_flow_quals.ref_id)))
     FULL JOIN ref_flow_inds ON ((refs.id = ref_flow_inds.ref_id)))
     FULL JOIN release_node_quants ON (((releases.id = release_node_quants.release_id) AND (contexts.id = release_node_quants.context_id))))
     FULL JOIN quants ON ((quants.id = release_node_quants.quant_id)))
     FULL JOIN release_node_quals ON (((releases.id = release_node_quals.release_id) AND (contexts.id = release_node_quals.context_id))))
     FULL JOIN quals ON ((quals.id = release_node_quals.qual_id)))
     FULL JOIN release_node_inds ON (((releases.id = release_node_inds.release_id) AND (contexts.id = release_node_inds.context_id))))
     FULL JOIN inds ON ((inds.id = release_node_inds.ind_id)))
  WHERE ((refs.id IN ( SELECT DISTINCT flows.ref_id
           FROM flows)) AND (commodity_names.is_default OR (commodity_names.is_default IS NULL)) AND (node_names.is_default OR (node_names.is_default IS NULL)))
  GROUP BY releases.id, contexts.id, refs.country_id, refs.version, node_names.name, refs.com_id, commodity_names.name, refs.id, refs.title, refs.quality_control_passed, ref_node_roles.columns, ref_flow_quants.flow_quants, ref_flow_quals.flow_quals, ref_flow_inds.flow_inds
  ORDER BY releases.id, node_names.name, commodity_names.name, refs.title;

Columns

View Definition

Possibly Referenced Tables/Views