recursive query to get all children

Question

I have an edibles table with the following definition:

 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 id     | integer               |           | not null |
 name   | character varying(60) |           | not null |
 parent | integer               |           | not null |

It has data such as:

 id |         name         | parent
----+----------------------+--------
  1 | fruit                |      0
  2 | veg                  |      0
  3 | cruciferous          |      2
  4 | horseradish          |      3
  5 | colanaceae           |      1
  6 | tomatoes             |      5
  7 | aubergine            |      5
  8 | chinese eggplant     |      7
  9 | costoluto fiorentino |      6
 10 | calvaceae            |      0

The table is self-referencing to cater for the parent-child relationship of the edibles.

I am trying to write a query where I get all children, no matter how nested for a given id.

Desired output

where id = 1 should return

 id |         name         | parent
----+----------------------+--------
  1 | fruit                |      0
  5 | colanaceae           |      1
  6 | tomatoes             |      5
  7 | aubergine            |      5
  8 | chinese eggplant     |      7
  9 | costoluto fiorentino |      6

where id = 10 should return

 id |         name         | parent
----+----------------------+--------
 10 | calvaceae            |      0

and finally where id = 6 should return:

 id |         name         | parent
----+----------------------+--------
  6 | tomatoes             |      5
  9 | costoluto fiorentino |      6

I haven't done any recursive SQL before, and I'm not really sure where to start.

I have created a db-fiddle too.

Update

Sorry, forgot to mention, the Postgres version is 10.

Answer 1

Schema (PostgreSQL v12)

-- create the table
create table edibles (
  id integer not null,
  name varchar(60) not null,
  parent integer not null);
  
-- insert data
insert into edibles (id, name, parent) values
  (1, 'fruit', 0),
  (2, 'veg', 0),
  (3, 'cruciferous', 2),
  (4, 'horseradish', 3),
  (5, 'colanaceae', 1),
  (6, 'tomatoes', 5),
  (7, 'aubergine', 5),
  (8, 'chinese eggplant', 7),
  (9, 'costoluto fiorentino', 6),
  (10, 'calvaceae', 0);

---

Query #1

with RECURSIVE cte as 
(
  select * from edibles where id=1
  union all
  select e.* from edibles e inner join cte on e.parent=cte.id
)
select * from cte;

id	name	parent
1	fruit	0
5	colanaceae	1
6	tomatoes	5
7	aubergine	5
8	chinese eggplant	7
9	costoluto fiorentino	6

---

View on DB Fiddle

For ID=6

Schema (PostgreSQL v12)

Query #2

with RECURSIVE cte as 
(
  select * from edibles where id=6
  union all
  select e.* from edibles e inner join cte on e.parent=cte.id
)
select * from cte;

id	name	parent
6	tomatoes	5
9	costoluto fiorentino	6

---

View on DB Fiddle

Answer 2

The recursive CTE is actually pretty simple:

with recursive cte as (
      select 6 as id
-------------^ just change this value
      union all
      select e.id
      from cte join
           edibles e
           on e.parent = cte.id
     )
select *
from cte;

Here is a db<>fiddle.