By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36284 in the last week).
CREATE TABLE category (id int, name text, parent_id int);
INSERT INTO category (id, name, parent_id) VALUES
(1, 'name1', NULL),
(2, 'name2', 1),
(3, 'name3', 2),
(4, 'name4', 1),
(5, 'name5', 4),
(6, 'name6', NULL);
CREATE TABLE cat_prod (id int, product_id int, category_id int);
INSERT INTO cat_prod (id, product_id, category_id)
VALUES
(1, 1, 1),
(2, 1, 2),
(3, 2, 1),
(4, 3, 1),
(5, 4, 2),
(6, 5, 2),
(7, 6, 3); -- ? product 6 is missing on your demo sample?
CREATE TABLE product (id int, name text);
INSERT INTO product (id, name) VALUES
(1, 'name1'),
(2, 'name2'),
(3, 'name3'),
(4, 'name4'),
(5, 'name5');
✓
6 rows affected
✓
7 rows affected
✓
5 rows affected
hidden batch(es)
-- func to return id of category and all children
CREATE OR REPLACE FUNCTION f_cats_of_id(int)
RETURNS TABLE (category_id int) AS
$func$
WITH RECURSIVE cte AS (
SELECT $1 AS parent_id -- not checking existence, cheapest
UNION ALL
SELECT c.id
FROM cte
JOIN category c USING (parent_id)
)
TABLE cte
$func$ LANGUAGE SQL;
✓
hidden batch(es)
SELECT DISTINCT p.*
FROM f_cats_of_id(1) c
JOIN cat_prod cp USING (category_id)
JOIN product p ON p.id = cp.product_id;
