no way to compare when less than two revisions
Différences
Ci-dessous, les différences entre deux révisions de la page.
— | support:trucs_et_astuces:trouver_tous_les_enregistrements_filles_d_un_enregistrement [2010/04/20 17:27] (Version actuelle) – créée sparky | ||
---|---|---|---|
Ligne 1: | Ligne 1: | ||
+ | Soit le script | ||
+ | |||
+ | < | ||
+ | CREATE TABLE test_ref ( | ||
+ | id integer, | ||
+ | tvalue text, | ||
+ | tparent integer | ||
+ | ); | ||
+ | |||
+ | |||
+ | INSERT INTO test_ref VALUES (1, ' | ||
+ | INSERT INTO test_ref VALUES (2, ' | ||
+ | INSERT INTO test_ref VALUES (3, ' | ||
+ | INSERT INTO test_ref VALUES (4, ' | ||
+ | INSERT INTO test_ref VALUES (5, ' | ||
+ | INSERT INTO test_ref VALUES (6, ' | ||
+ | INSERT INTO test_ref VALUES (7, ' | ||
+ | INSERT INTO test_ref VALUES (8, ' | ||
+ | INSERT INTO test_ref VALUES (9, ' | ||
+ | INSERT INTO test_ref VALUES (10, ' | ||
+ | INSERT INTO test_ref VALUES (14, ' | ||
+ | INSERT INTO test_ref VALUES (15, 'Sous departement procedure', | ||
+ | INSERT INTO test_ref VALUES (16, 'Sous sous departement procedure', | ||
+ | </ | ||
+ | |||
+ | Je souhaite trouver tout les enregistrements qui dépendent directement ou directement d'un département. | ||
+ | Par exemple : j' | ||
+ | |||
+ | La solution la plus simple est d' | ||
+ | grande table, ce n'est pas la solution optimale | ||
+ | < | ||
+ | create or replace function get_tree(value integer) | ||
+ | returns setof test_ref | ||
+ | as | ||
+ | $_$ | ||
+ | declare | ||
+ | n integer; | ||
+ | i record; | ||
+ | e record; | ||
+ | begin | ||
+ | raise notice 'Call get_tree(%)', | ||
+ | for i in select * from test_ref where tparent=value loop | ||
+ | raise notice ' | ||
+ | return next i; | ||
+ | |||
+ | for e in select * from get_tree(i.id) loop | ||
+ | raise notice ' | ||
+ | return next e; | ||
+ | end loop; | ||
+ | |||
+ | end loop; | ||
+ | |||
+ | raise notice 'Call get_tree(%) finished ', value; | ||
+ | return; | ||
+ | |||
+ | |||
+ | |||
+ | end; | ||
+ | $_$ language plpgsql; | ||
+ | </ | ||
+ | |||
+ | Testons pour voir, tous les département dépendant du 1 (Département IT) | ||
+ | < | ||
+ | |||
+ | | ||
+ | |||
+ | |||
+ | id | | ||
+ | ----+-----------------------------+--------- | ||
+ | 3 | Departement fourniture IT | ||
+ | 4 | Departement Machine IT | 1 | ||
+ | 5 | Departement Server | ||
+ | 7 | Departement logiciel server | 5 | ||
+ | 6 | Departemet Machine bureau | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | |||