title: Postgresql: split the ltree and get path name using join
body: <p>I am trying to change the name of path using another table in Postgresql.</p>
  <p>'''</p>
  <pre><code>--table with ltree path
  create table book_path( id serial primary key, book_id int,bpath ltree)
  
  insert into book_path(book_id, bpath) values (1, '1');
  insert into book_path(book_id, bpath) values (2, '1.2');
  insert into book_path(book_id, bpath) values (3, '1.2.3');
  insert into book_path(book_id, bpath) values (4, '1.2.4');
  insert into book_path(book_id, bpath) values (5, '1.5');
  insert into book_path(book_id, bpath) values (5, '1.5.6');
  insert into book_path(book_id, bpath) values (5, '1.5.7');
  
  --table with book details
  create table books(id int, sub text);
  
  insert into books(id,sub) values (1, 'Computer');
  insert into books(id,sub) values (2, 'Database');
  insert into books(id,sub) values (3, 'Oracle');
  insert into books(id,sub) values (4, 'MySQL');
  insert into books(id,sub) values (5, 'Programming');
  insert into books(id,sub) values (6, 'Java');
  insert into books(id,sub) values (7, 'C++');
  </code></pre>
  <p>'''</p>
  <p>I am trying to pull names of book from &quot;books&quot; table like below:</p>
  <p>id    bpath   actual_path
  1     1       Computer
  2     1.2     Computer.Database
  3     1.2.3   Computer.Database.Oracle</p>
  <p>but not able to get actual path:</p>
  <pre><code>select bkp.id,bkp.bpath,ltree2text(subpath(bkp.bpath, -1, 1))::int as child,b.sub AS actual_path
  from book_path bkp inner join books b on b.id=ltree2text(subpath(bkp.bpath, -1, 1))::int 
  </code></pre>
  <p>Could you please assist how I can achieve this type of output by joining tables.</p>