pggeodb.nancy.inra.fr/db_woodseer - db_woodseer on pggeodb.nancy.inra.fr |
|
View: v_metadata_zone
pggeodb.nancy.inra.fr/db_woodseer - db_woodseer on pggeodb.nancy.inra.fr |
|
View: v_metadata_zone
Schema
Owner
albenard
Descriptions
View allowing the display of zone measurements (variables / metadata)
Fields
Name | Data type | Description |
|---|---|---|
id_child |
integer |
|
ltree_child |
public.ltree |
|
id_parent |
integer |
|
ltree_parent |
public.ltree |
|
date_measure |
date |
|
var_nname |
varchar(50) |
|
var_code |
varchar(25) |
|
value |
text |
|
prot_description |
varchar(255) |
|
uni_name |
varchar(50) |
|
uni_symbol |
varchar(15) |
|
Rules
Name | Event | Instead | Condition | Description |
|---|---|---|---|---|
_RETURN |
SELECT |
|
|
|
Indices
There are no indices for table v_metadata_zone
Definition
id_child,
ltree_child,
id_parent,
ltree_parent,
date_measure,
var_nname,
var_code,
value,
prot_description,
uni_name,
uni_symbol)
AS
WITH full_zone AS (
SELECT zon1.zon_id AS id_child,
zon1.zon_tree AS ltree_child,
zon2.zon_id AS id_parent,
zon2.zon_tree AS ltree_parent
FROM tr_study_zone_zon zon1
JOIN tr_study_zone_zon zon2 ON zon2.zon_tree @> zon1.zon_tree
ORDER BY zon1.zon_tree, zon2.zon_tree
)
SELECT full_zone.id_child,
full_zone.ltree_child,
full_zone.id_parent,
full_zone.ltree_parent,
t_measure_mzon.mzon_date_measurement AS date_measure,
tr_variable_var.var_name AS var_nname,
tr_variable_var.var_code,
COALESCE(t_measure_mzon.mzon_value_num::text,
t_measure_mzon.mzon_value_text::text, t_measure_mzon.mzon_value_date::text) AS value,
tr_protocol_prot.prot_description,
tr_unit_uni.uni_name,
tr_unit_uni.uni_symbol
FROM full_zone
JOIN t_measure_mzon ON full_zone.id_parent = t_measure_mzon.mzon_zon_id
JOIN tr_variable_var ON t_measure_mzon.mzon_var_id = tr_variable_var.var_id
LEFT JOIN tr_unit_uni ON t_measure_mzon.mzon_uni_id = tr_unit_uni.uni_id
LEFT JOIN tr_protocol_prot ON t_measure_mzon.mzon_prot_id =
tr_protocol_prot.prot_id;
COMMENT ON VIEW public.v_metadata_zone
IS 'View allowing the display of zone measurements (variables / metadata)';
This file was generated with SQL Manager for PostgreSQL (www.pgsqlmanager.com) at 07/12/2018 13:23 |
|