Sql, get category tree for values from one table, names and ids
Example.

Structure and data:



CREATE TABLE tree (id INT, parent INT, name VARCHAR);

INSERT INTO tree VALUES
(1,0,'root'),
(2,1,'branch 1'),
(3,1,'branch 2'),
(4,2,'branch 3'),
(5,2,'branch 4'),
(6,4,'branch 5'),
(7,6,'branch 6'),
(8,3,'leaf 1'),
(9,5,'leaf 2'),
(10,7,'leaf 3');


Query:

WITH RECURSIVE cte AS ( SELECT t1.id, t1.parent, t1.name
                        FROM tree t1
                        LEFT JOIN tree t2 ON t1.id = t2.parent
                        WHERE t2.parent is null
                        UNION ALL
                        SELECT t1.id, t1.parent, CONCAT(t1.name, ' &gt; ', t2.name)
                        FROM tree t1
                        JOIN cte t2 ON t1.id = t2.parent 
                      )
SELECT name
FROM cte
WHERE parent = 0;


Output:

                    name
---------------------------------------------------------
root &gt; branch 2 &gt; leaf 1
root &gt; branch 1 &gt; branch 4 &gt; leaf 2
root &gt; branch 1 &gt; branch 3 &gt; branch 5 &gt; branch 6 &gt; leaf 3


fiddle



UPDATE: 

WITH recursive cte AS ( SELECT t1.category_id, t1.category_parent,    t1.category_name::text
                    FROM s_category t1
                    LEFT JOIN s_category t2 ON t1.category_id = t2.category_parent
                    WHERE t2.category_parent is null
                    UNION ALL
                    SELECT t1.category_id, t1.category_parent,     CONCAT(t1.category_name, ' &gt; ', t2.category_name)
                    FROM s_category t1
                    JOIN cte t2 ON t1.category_id = t2.category_parent 
                  )
select 

cte.category_name AS category,
s_product.product_id as itemID,
s_product.product_id as itemGroupID,
product_shop_id as productCode,
product_ean as ean,
product_name as title,
product_short_label as description,
product_url as link,
filename as image,
price_tax as price,
price_rec as priceOriginal,
producer_name as brand,
-- as category,
s_category.category_id as categoryID
-- as hierarchy,
-- as params,
-- as name,
-- as value,
-- as labels

from

public.s_product
left join s_product_image on s_product.product_id =             s_product_image.product_id
left join s_pricelist_generated_lists on s_product.product_id = s_pricelist_generated_lists.product_id
left join cte on s_product.category_id = cte.category_id
left join s_producer on s_product.producer_id = s_producer.producer_id

where

s_product.product_id='133471'
and s_pricelist_generate


UPDATE2:

Alternative CTE - producing URL for each node.

WITH RECURSIVE 
cte AS ( SELECT id, parent, name
         FROM tree
         WHERE parent = 0
       UNION ALL
         SELECT tree.id, tree.parent, CONCAT(cte.name, ' &gt; ', tree.name)
         FROM tree, cte
         WHERE cte.id = tree.parent
       )