PostgreSQL array, recursion and more - Mailing list pgsql-sql

MESH Data Tree:

example:
Hallux;A01.378.610.250.300.792.380

where:
A01 is Body Regions
A01.378 is Extremities
A01.378.610 is Lower Extremity
A01.378.610.250 is Foot
A01.378.610.250.300 is Forefoot, Human
A01.378.610.250.300.792 is Toes

CREATE OR REPLACE FUNCTION mesh_split(text) RETURNS text[]
AS $$
return [split('\.',$_[0])];
$$ LANGUAGE plperlu;

arancia=# select mesh_split('A01.378.610.250.300.792.380');
           mesh_split
-------------------------------
  {A01,378,610,250,300,792,380}
(1 row)


/*
    Is it a real array?
    If it is, why can I not use index to access its items?
  */

arancia=# select mesh_split('A01.378.610.250.300.792.380')[1];
ERROR:  syntax error at or near "["
LINE 1: select mesh_split('A01.378.610.250.300.792.380')[1];
                                                         ^
/*
     but it is an array, it behaves as it is.
  */
arancia=> select array_length(mesh_split('A01.378.610.250.300.792.380'),1);
  array_length
--------------
             7
(1 row)

/* How to get access to its items then?
  */


Another problem related:

arancia=> select * from meshtree where code = ANY
mesh_split('A01.378.610.250.300.792.380');
ERROR:  syntax error at or near "mesh_split"
LINE 1: select * from meshtree where code = ANY mesh_split('A01.378....
                                                 ^

select * from meshtree,
unnest(mesh_split('A01.378.610.250.300.792.380')) as c where
c=meshtree.code;
  parent |  id   | code |                            description

--------+-------+------+-------------------------------------------------------------------
      10 |    11 | 300  | Dehydroepiandrosterone Sulfate
      33 |    34 | 250  | Cymarine
      48 |    49 | 250  | Cymarine
      61 |    62 | 250  | Dihydrotachysterol
      66 |    68 | 300  | Calcitriol
      65 |    69 | 250  | Calcifediol
      92 |    93 | 380  | Glycodeoxycholic Acid
      98 |    99 | 250  | Finasteride
     111 |   117 | 300  | Chenodeoxycholic Acid
     145 |   146 | 300  | Dehydroepiandrosterone Sulfate
     180 |   182 | 250  | Ethinyl Estradiol-Norgestrel Combination
     190 |   191 | 250  | Desoximetasone
[..]
         | 18638 | A01  | Body Regions
[..]
     190 |   192 | 300  | Dexamethasone Isonicotinate
     195 |   196 | 250  | Clobetasol
     199 |   200 | 300  | Fluocinonide
     206 |   207 | 250  | Diflucortolone
     266 |   267 | 300  | Dexamethasone Isonicotinate
     281 |   282 | 250  | Diflucortolone
     290 |   293 | 250  | Dehydrocholesterols
     305 |   306 | 250  | Dihydrotachysterol
     312 |   314 | 300  | Calcitriol
     311 |   315 | 250  | Calcifediol
     320 |   321 | 250  | Cholestanol
     328 |   330 | 300  | Calcitriol
[..]
   52135 | 52136 | 250  | Eye Injuries
   52136 | 52137 | 250  | Eye Burns
   52149 | 52155 | 300  | Hematoma, Epidural, Cranial
   52181 | 52196 | 300  | Gallbladder Emptying
   52269 | 52277 | 300  | Caplan Syndrome
   52360 | 52368 | 300  | Caplan Syndrome
   52428 | 52442 | 380  | Hemothorax
   52476 | 52491 | 610  | Pneumonia
   52534 | 52535 | 380  | Legionnaires' Disease
(2204 rows)

I really want to write better similar query:

arancia=> with recursive t(id,parent,codeparts,idx,last,descriptions) as (
   SELECT
id, parent, mesh_split('A01.378.610.250.300.792.380'), 1,
array_length(mesh_split('A01.378.610.250.300.792.380'),1),
ARRAY[description]
     FROM meshtree WHERE code='A01'
     UNION ALL
   SELECT m.id, m.parent, t.codeparts, idx+1, last, descriptions ||
ARRAY[description]
     FROM meshtree AS m JOIN t ON (t.id=m.parent)
    WHERE idx<=last AND m.code=t.codeparts[idx+1])
  SELECT t.* FROM t;
   id   | parent |           codeparts           | idx | last |
                           descriptions
-------+--------+-------------------------------+-----+------+--------...
  18638 |        | {A01,378,610,250,300,792,380} |   1 |    7 | {"Body
Regions"}
  18675 |  18638 | {A01,378,610,250,300,792,380} |   2 |    7 | {"Body
Regions",Extremities}
  18676 |  18675 | {A01,378,610,250,300,792,380} |   3 |    7 | {"Body
Regions",Extremities,"Lower Extremity"}
  18679 |  18676 | {A01,378,610,250,300,792,380} |   4 |    7 | {"Body
Regions",Extremities,"Lower Extremity",Foot}
  18682 |  18679 | {A01,378,610,250,300,792,380} |   5 |    7 | {"Body
Regions",Extremities,"Lower Extremity",Foot,"Forefoot, Human"}
  18683 |  18682 | {A01,378,610,250,300,792,380} |   6 |    7 | {"Body
Regions",Extremities,"Lower Extremity",Foot,"Forefoot, Human",Toes}
  18684 |  18683 | {A01,378,610,250,300,792,380} |   7 |    7 | {"Body
Regions",Extremities,"Lower Extremity",Foot,"Forefoot, Human",Toes,Hallux}
(7 rows)

explain analyze with recursive
t(id,parent,codeparts,idx,last,descriptions) as (
   select

id,parent,mesh_split('A01.378.610.250.300.792.380'),1,array_length(mesh_split('A01.378.610.250.300.792.380'),1),ARRAY[description]

from meshtree where code='A01'
   union all
   select m.id,m.parent,t.codeparts,idx+1,last,descriptions ||
ARRAY[description] from meshtree as m join t on (t.id=m.parent) where
idx<=last and m.code=t.codeparts[idx+1]) select t.* from t;

QUERY PLAN


------------------------------------------------------------------------------------------------------------------------------------------------------
  CTE Scan on t  (cost=6336.53..6337.17 rows=32 width=80) (actual
time=4.850..9.453 rows=7 loops=1)
    CTE t
      ->  Recursive Union  (cost=0.00..6336.53 rows=32 width=99) (actual
time=4.839..9.397 rows=7 loops=1)
            ->  Index Scan using meshtree_id_code on meshtree
(cost=0.00..1030.38 rows=22 width=27) (actual time=4.828..8.895 rows=1
loops=1)
                  Index Cond: (code = 'A01'::text)
            ->  Nested Loop  (cost=0.00..530.55 rows=1 width=99) (actual
time=0.051..0.061 rows=1 loops=7)
                  ->  WorkTable Scan on t  (cost=0.00..4.95 rows=73
width=76) (actual time=0.005..0.008 rows=1 loops=7)
                        Filter: (idx <= last)
                  ->  Index Scan using meshtree_parent_code on meshtree
m  (cost=0.00..7.18 rows=1 width=31) (actual time=0.031..0.034 rows=1
loops=7)
                        Index Cond: ((m.parent = t.id) AND (m.code =
t.codeparts[(t.idx + 1)]))
  Total runtime: 9.758 ms
(11 rows)


PostgreSQL rocks!


Thank you in advance,              \ferz