vw_acciones_contexto

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

id_accion

int8

√

null

codigo

text

2147483647

√

null

nombre_proyecto

varchar

5000

√

null

vigencia_inicio

date

√

null

vigencia_final

date

√

null

impacto

componente_enm

2147483647

√

null

eje

text

2147483647

√

null

medida

text

2147483647

√

null

tipo_medida

text

2147483647

√

null

indicador

text

2147483647

√

null

municipio

text

2147483647

√

null

subzona_hidrografica

text

2147483647

√

null

servicios_ecosistemicos

text

2147483647

√

null

ecosistema_intervenido

text

2147483647

√

null

fuente

text

2147483647

√

null

entidad_responsable

text

2147483647

√

null

inversion_cop

float8

17,17

√

null

Relationships

Close relationships within degrees of separation

View Definition

WITH ejes_agg AS (
         SELECT ia_1.id_accion_fk,
            string_agg(DISTINCT (e.nombre)::text, ', '::text ORDER BY (e.nombre)::text) AS ejes
           FROM (((indicador_accion ia_1
             JOIN indicador i ON ((i.id = ia_1.id_indicador_fk)))
             JOIN medida m ON ((m.id = i.id_medida_fk)))
             JOIN eje e ON ((e.id = m.id_eje_fk)))
          GROUP BY ia_1.id_accion_fk
        ), medidas_agg AS (
         SELECT ia_1.id_accion_fk,
            string_agg(DISTINCT m.nombre, ', '::text ORDER BY m.nombre) AS medidas,
            string_agg(DISTINCT (m.alcance_medida_enm)::text, ', '::text ORDER BY (m.alcance_medida_enm)::text) AS tipos_medida
           FROM ((indicador_accion ia_1
             JOIN indicador i ON ((i.id = ia_1.id_indicador_fk)))
             JOIN medida m ON ((m.id = i.id_medida_fk)))
          GROUP BY ia_1.id_accion_fk
        ), indicadores_agg AS (
         SELECT ia_1.id_accion_fk,
            string_agg(DISTINCT COALESCE(((i.codigo || ' - '::text) || (i.nombre)::text), (i.nombre)::text), ', '::text ORDER BY COALESCE(((i.codigo || ' - '::text) || (i.nombre)::text), (i.nombre)::text)) AS indicadores
           FROM (indicador_accion ia_1
             JOIN indicador i ON ((i.id = ia_1.id_indicador_fk)))
          GROUP BY ia_1.id_accion_fk
        ), municipios_agg AS (
         SELECT ma_1.id_accion_fk,
            string_agg(DISTINCT (mu.nombre)::text, ', '::text ORDER BY (mu.nombre)::text) AS municipios
           FROM (municipio_accion ma_1
             JOIN municipio mu ON ((mu.id = ma_1.id_municipio_fk)))
          GROUP BY ma_1.id_accion_fk
        ), subzona_agg AS (
         SELECT asz.id_accion_fk,
            string_agg(DISTINCT dsz.nombre, ', '::text ORDER BY dsz.nombre) AS subzonas
           FROM (accion_dom_subzona_hidrografica asz
             JOIN dom_subzona_hidrografica dsz ON ((dsz.id = asz.id_dom_subzona_hidrografica_fk)))
          GROUP BY asz.id_accion_fk
        ), eco_agg AS (
         SELECT ae.id_accion_fk,
            string_agg(DISTINCT de.nombre, ', '::text ORDER BY de.nombre) AS servicios_eco
           FROM (accion_dom_servicios_eco_sbn ae
             JOIN dom_servicios_eco_sbn de ON ((de.id = ae.id_dom_servicios_eco_sbn_fk)))
          GROUP BY ae.id_accion_fk
        ), ecosistema_agg AS (
         SELECT ea_1.id_accion_fk,
            string_agg(DISTINCT ec.nombre, ', '::text ORDER BY ec.nombre) AS ecosistemas_intervenidos
           FROM (ecosistema_accion ea_1
             JOIN ecosistema ec ON ((ec.id = ea_1.id_ecosistema_fk)))
          GROUP BY ea_1.id_accion_fk
        ), fuentes_agg AS (
         SELECT ff.id_accion_fk,
            string_agg(DISTINCT (ff.nombre)::text, ', '::text ORDER BY (ff.nombre)::text) AS fuentes,
            sum(ff.valor) AS inversion_cop
           FROM fuente_financiacion_accion ff
          GROUP BY ff.id_accion_fk
        )
 SELECT a.id AS id_accion,
    a.codigo,
    a.nombre_proyecto,
    a.vigencia_inicio,
    a.vigencia_final,
    a.componente AS impacto,
    COALESCE(ea.ejes, 'Sin eje'::text) AS eje,
    COALESCE(ma.medidas, 'Sin medida'::text) AS medida,
    COALESCE(ma.tipos_medida, 'Sin dato'::text) AS tipo_medida,
    COALESCE(ia.indicadores, 'Sin indicador'::text) AS indicador,
    COALESCE(mun.municipios, 'Sin municipio'::text) AS municipio,
    COALESCE(sz.subzonas, 'Sin dato'::text) AS subzona_hidrografica,
    COALESCE(eco.servicios_eco, 'Sin dato'::text) AS servicios_ecosistemicos,
    COALESCE(ecos.ecosistemas_intervenidos, 'Sin dato'::text) AS ecosistema_intervenido,
    COALESCE(fu.fuentes, 'Sin fuente'::text) AS fuente,
    COALESCE(ent.nombre, 'Sin entidad'::text) AS entidad_responsable,
    COALESCE(fu.inversion_cop, (0)::double precision) AS inversion_cop
   FROM ((((((((((accion a
     LEFT JOIN ejes_agg ea ON ((ea.id_accion_fk = a.id)))
     LEFT JOIN medidas_agg ma ON ((ma.id_accion_fk = a.id)))
     LEFT JOIN indicadores_agg ia ON ((ia.id_accion_fk = a.id)))
     LEFT JOIN municipios_agg mun ON ((mun.id_accion_fk = a.id)))
     LEFT JOIN subzona_agg sz ON ((sz.id_accion_fk = a.id)))
     LEFT JOIN eco_agg eco ON ((eco.id_accion_fk = a.id)))
     LEFT JOIN ecosistema_agg ecos ON ((ecos.id_accion_fk = a.id)))
     LEFT JOIN fuentes_agg fu ON ((fu.id_accion_fk = a.id)))
     LEFT JOIN usuario u ON ((u.id = a.id_usuario_fk)))
     LEFT JOIN entidad ent ON ((ent.id = u.id_entidad_fk)))
  WHERE ((a.activo = true) AND (a.estado_registro = 'Validado'::estado_registro_enm));

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views