db_ecological_db
Previous topic Chapter index Next topic

View: v_metadonnees_zone

 

 

Schema

public

 

Owner

postgres

 

Descriptions

Vue permettant l'affichage des mesures de zones (variables / métadonnées)

 

Fields

Name

Data type

Description

id_enfant

integer

 

ltree_enfant

public.ltree

 

id_parent

integer

 

ltree_parent

public.ltree

 

date_mesure

date

 

var_nom

varchar(50)

 

var_code

varchar(25)

 

valeur

text

 

prot_description

varchar(255)

 

uni_nom

varchar(50)

 

uni_symbole

varchar(15)

 

 

Rules

Name

Event

Instead

Condition

Description

_RETURN

SELECT

 

 

 

Indices

There are no indices for table v_metadonnees_zone

 

Definition

CREATE VIEW public.v_metadonnees_zone (
   id_enfant,
   ltree_enfant,
   id_parent,
   ltree_parent,
   date_mesure,
   var_nom,
   var_code,
   valeur,
   prot_description,
   uni_nom,
   uni_symbole)
AS
WITH
full_zone AS (
SELECT zon1.zon_id AS id_enfant,
           zon1.zon_tree AS ltree_enfant,
           zon2.zon_id AS id_parent,
           zon2.zon_tree AS ltree_parent
FROM tr_zone_etude_zon zon1
            JOIN tr_zone_etude_zon zon2 ON zon2.zon_tree @> zon1.zon_tree
ORDER BY zon1.zon_tree, zon2.zon_tree
       )
   SELECT full_zone.id_enfant,
   full_zone.ltree_enfant,
   full_zone.id_parent,
   full_zone.ltree_parent,
   t_mesure_mzon.mzon_date_mesure AS date_mesure,
   tr_variable_var.var_nom,
   tr_variable_var.var_code,
   COALESCE(t_mesure_mzon.mzon_valeur_num::text,
       t_mesure_mzon.mzon_valeur_text::text, t_mesure_mzon.mzon_valeur_date::text) AS valeur,
   tr_protocole_prot.prot_description,
   tr_unite_uni.uni_nom,
   tr_unite_uni.uni_symbole
   FROM full_zone
    JOIN t_mesure_mzon ON full_zone.id_parent = t_mesure_mzon.mzon_zon_id
    JOIN tr_variable_var ON t_mesure_mzon.mzon_var_id = tr_variable_var.var_id
    LEFT JOIN tr_unite_uni ON t_mesure_mzon.mzon_uni_id = tr_unite_uni.uni_id
    LEFT JOIN tr_protocole_prot ON t_mesure_mzon.mzon_prot_id =
        tr_protocole_prot.prot_id;

COMMENT ON VIEW public.v_metadonnees_zone
IS 'Vue permettant l''affichage des mesures de zones (variables / métadonnées)';

This file was generated with SQL Manager for PostgreSQL (www.pgsqlmanager.com)
Previous topic Chapter index Next topic