db_toff on pggeodb.nancy.inra.fr

View: v_tree_families_traits

Schema

public

Owner

postgres

Descriptions

View to produce recursively the whole trait family tree / vue permettant de créer récursivement l'arbre complet des familles de trait.

Fields

Rules

Indices

There are no indices for table v_tree_families_traits

Definition

CREATE VIEW public.v_tree_families_traits (
   description,
   id_family,
   id_parent,
   stage,
   path)
AS
WITH RECURSIVE trail_family_trait(description, id_family, id_parent, stage, path) AS (
SELECT tr_family_trait_fam.fam_name,
           tr_family_trait_fam.fam_id,
           tr_family_trait_fam.fam_parent_id,
           0,
           tr_family_trait_fam.fam_name::text AS fam_name
FROM tr_family_trait_fam
WHERE tr_family_trait_fam.fam_parent_id IS NULL
UNION ALL
SELECT fam.fam_name,
           fam.fam_id,
           fam.fam_parent_id,
           pf.stage + 1,
           (pf.path || '/'::text) || fam.fam_name::text
FROM tr_family_trait_fam fam
            JOIN trail_family_trait pf ON pf.id_family = fam.fam_parent_id
       )
   SELECT trail_family_trait.description,
   trail_family_trait.id_family,
   trail_family_trait.id_parent,
   trail_family_trait.stage,
   trail_family_trait.path
   FROM trail_family_trait
   ORDER BY trail_family_trait.path;

COMMENT ON VIEW public.v_tree_families_traits
IS 'View to produce recursively the whole trait family tree / vue permettant de créer récursivement l''arbre complet des familles de trait.';

This file was generated with SQL Manager for PostgreSQL (www.pgsqlmanager.com) at 19/06/2019 10:17