Recursive CTE for building menus - Mailing list pgsql-general

Hello,

Whilst researching current thinking on hierarchical queries in
Postgres, I stumbled accross this excellent blog post:

https://illuminatedcomputing.com/posts/2014/09/postgres-cte-for-threaded-comments/

But try as I might, my SQL-foo is not up to scratch to adapt it to my
needs, I keep on loosing child nesting and other weird bug-dom.

My table looks like this :
menu_title text
menu_item_id text
menu_priority integer
menu_parent text

The adaptions I am trying to make are as follows:
- Higher priority moves the item higher up the menu (i.e. adapting
from the original "votes" concept).
- Default alphabetical ordering of titles
- Use of alphanumeric IDs instead of numeric

The only thing that I can get consistently working is the alphanumeric menu IDs.

For menu priorities, postgres does not seem to like mixing numeric and
alphanumeric in an array:
ERROR:  ARRAY types integer and text cannot be matched
LINE 3:  array[-menu_priority,menu_itemid] as path,1 as depth

insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('Home','H',1000,NULL);
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('About','A',900,NULL);
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('FOOBAR','F',800,NULL);
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('Resources','R',NULL,'A');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('Background','B',NULL,'A');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('Foo','Ff',NULL,'F');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('About Bar','Fba',NULL,'Fb');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('Team Bar','Fbt',NULL,'Fb');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('Bar','Fb',NULL,'F');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('Foo World','Ffw',NULL,'Ff');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('About World','FFwa',NULL,'Ffw');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('World Introduction','FFwi',1000,'Ffw');

N.B. Although I show NULL as a default priority, I have experimenting
with setting default priorities with no success.

The expected outcome from the above would be (ignore the pretty-print
elements, its just to help human parsing !):
•Home
•About
-> Background
-> Resources
•FOOBAR
-> Bar
->-> About Bar
->-> Team Bar
-> Foo
->-> Foo World
->->-> World Introduction
->->-> About World