Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE local (local_id serial PRIMARY KEY, parent_id integer, name text);
INSERT INTO local VALUES
  (1, NULL, 'A')
, (2, 1, 'B')
, (3, 1, 'C')
, (4, 3, 'D')
, (5, NULL, 'E')
, (6, 5, 'F')
;

CREATE TABLE action (action_id int, local_id int, category_id int);
INSERT INTO action VALUES
  (1, 1, 2)
, (2, 3, 1)
, (3, 5, 1)
, (4, 6, 1)
;

CREATE TABLE element	(element_id int, local_id int, name text, category_id int);
INSERT INTO element VALUES
  (1, 1, 'A', 1)
, (2, 2, 'B', 2)
, (3, 2, 'C', 1)
, (4, 4, 'D', 2)
, (5, 5, 'E', 2)
, (6, 6, 'F', 1)
, (7, 6, 'G', 1)
;

-- Table category is irrelevant to the problem
6 rows affected

4 rows affected

7 rows affected

CREATE OR REPLACE FUNCTION f_elems(_action_id integer, OUT element_id int)
  RETURNS SETOF integer
  LANGUAGE sql STABLE AS
$func$
WITH RECURSIVE l AS (
   SELECT a.category_id, l.local_id
   FROM   action a
   JOIN   local  l USING (local_id)
   WHERE  a.action_id = $1

   UNION ALL 
   SELECT l.category_id, c.local_id
   FROM   l
   JOIN   local c ON c.parent_id = l.local_id   -- c for child
   )
SELECT e.element_id
FROM   l
JOIN   element e USING (category_id, local_id)
$func$;
SELECT f_elems(1);


f_elems


2

4


SELECT f_elems(2);
SELECT f_elems(3);


f_elems


6

7