Postgres recursive queries and window functions to produce tree from table

Let’s say I have the following data.

CREATE TABLE ORDERS(
  id INT PRIMARY KEY,
  category TEXT,
  subcategory TEXT,
  description TEXT
);

INSERT INTO TABLE ORDERS VALUES
(1001, "CAT1"   "SUBCAT1"   "DESC1"),
(1002, "CAT1"   "SUBCAT1"   "DESC2"),
(1003, "CAT1"   "SUBCAT2"   "DESC3"),
(1004, "CAT1"   "SUBCAT2"   "DESC4"),
(1005, "CAT1"   "SUBCAT2"   "DESC5"),
(1006, "CAT2"   "SUBCAT3"   "DESC6"),
(1007, "CAT2"   "SUBCAT3"   "DESC7"),
(1008, "CAT2"   "SUBCAT4"   "DESC8"),

I got the request to produce a table with a single column in which categories and subcategories appear arranged like this.

result
-------------
CAT1
  -> SUBCAT1
    -> DESC1
    -> DESC2
    -> DESC3
  -> SUBCAT2
    -> DESC4
    -> DESC5
CAT2
  -> SUBCAT3
    -> DESC6
    -> DESC7
  -> SUBCAT4
    -> DESC8

I don’t understand how to complete the task with the requirements established by my client (especially the first one). These are:

• I should use two new columns:
  • parent: to reference the parent level category.
  • seq_orders: to build a sequence
• I should use window functions and a recursive query to build the tree-like column

I’m hoping that someone who sees this from the outside could provide some help.

Thanks!

---

EDIT

It’s necessary to (somehow) follow this process as requested by the client:

1. Create column parent to refer to the higher level category. Higher-level categories will have this field as null.
2. Create a sequence called seq_orders that starts with 1 and increases by one unit.
3. Insert into the table orders the different levels of level 1 (category field) and level 2 (subcategory field) using the above sequence to assign the different ids. In Level 2 and Level 3 the parent field has to point to the corresponding higher level category.
4. Remove the redundant fields category and subcategory.

Answer

You actually don’t want a recursive CTE. You want to unpivot the data and add in rows for each level. This looks like:

select x.which
from ((select '    -> ' || o.description as which, array[o.category, o.subcategory, o.description] as ord
       from orders o
       group by o.category, o.subcategory, o.description
      ) union all
      (select '  -> ' || o.subcategory, array[o.category, o.subcategory]
       from orders o
       group by o.category, o.subcategory
      ) union all
      (select o.category, array[o.category]
       from orders o
       group by o.category
      )
     ) x
order by ord;

Here is a recursive CTE.