title: Join two tables using id and descendants from tree like table
body: <p>I have the following tables:</p>
  
  <pre><code>CREATE TABLE element (
    element_id serial PRIMARY KEY,
    local_id integer,
    name varchar,
    CONSTRAINT fk_element_local_id FOREIGN KEY (local_id)
        REFERENCES local (local_id) MATCH SIMPLE
        ON UPDATE NO ACTION ON DELETE NO ACTION
  );
  
  CREATE TABLE local (
    local_id serial PRIMARY KEY,
    parent_id integer,
    name varchar,
    CONSTRAINT fk_local_parent_id_local_id FOREIGN KEY (parent_id)
        REFERENCES local (local_id) MATCH SIMPLE
        ON UPDATE CASCADE ON DELETE SET NULL
  );
  
  CREATE TABLE category (
    category_id serial PRIMARY KEY,
    name varchar
  );
  
  CREATE TABLE action (
    action_id serial PRIMARY KEY,
    local_id integer,
    category_id integer,
    CONSTRAINT fk_action_local_id FOREIGN KEY (local_id)
        REFERENCES local (local_id) MATCH SIMPLE
        ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT fk_action_element_id FOREIGN KEY (element_id)
        REFERENCES element (element_id) MATCH SIMPLE
        ON UPDATE NO ACTION ON DELETE NO ACTION
  );
  </code></pre>
  
  <p>I want to select all elements from an action. If the local from the element is descendant from the local of the action it should appear as well.<br>
  example:</p>
  
  <p>Table <code>local</code>:</p>
  
  <pre><code>|local_id | parent_id | name |
  |---------+-----------+------|
  |1        |NULL       |A     |
  |2        |1          |B     |
  |3        |1          |C     |
  |4        |3          |D     |
  |5        |NULL       |E     |
  |6        |5          |F     |
  |_________|___________|______|
  </code></pre>
  
  <p>Table <code>category</code>:</p>
  
  <pre><code>| category_id | name |
  |-------------+------|
  |1            |A     |
  |2            |B     |
  |2            |C     |
  |_____________|______|
  </code></pre>
  
  <p>Table <code>element</code>:</p>
  
  <pre><code>|element_id | local_id | name | category_id |
  |-----------+----------+------+-------------|
  |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           |
  |___________|__________|______|_____________|
  </code></pre>
  
  <p>Table <code>action</code>:</p>
  
  <pre><code>|action_id | local_id | category_id |
  |----------+----------+-------------|
  | 1        | 1        | 2           |
  | 2        | 3        | 1           |
  | 3        | 5        | 1           |
  | 4        | 6        | 1           |
  |__________|__________|_____________|
  </code></pre>
  
  <p>The results from query that I want:</p>
  
  <pre><code>CASE: action_id = 1
  return: element_id: 2,4
  
  CASE: action_id = 2
  return: element_id: null
  
  CASE: action_id = 3
  return: element_id: 6,7
  </code></pre>
  
  <p>I've made a function that returns all the descendants including the actual node but I'm having a hard time because of the performance when calling the function thousands of times.
  My function looks like this:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION fn_local_get_childs(_parent_id integer)
    RETURNS SETOF integer AS
  $BODY$
  DECLARE
     r integer;
  BEGIN
     FOR r IN SELECT local_id FROM local WHERE local_id IN ( 
        (WITH RECURSIVE parent AS
        (
           SELECT local_id , parent_id  from local WHERE local_id = _parent_id
           UNION ALL 
           SELECT t.local_id , t.parent_id FROM parent
           INNER JOIN local t ON parent.local_id =  t.parent_id
        )
        SELECT local_id FROM  parent
        ) 
     )
     LOOP
        RETURN NEXT r;
     END LOOP;
     RETURN;        
  END;
  $BODY$
    LANGUAGE plpgsql VOLATILE
    COST 100
    ROWS 1000;
  </code></pre>
  
  <p>And my ultra slow query looks like these:</p>
  
  <pre><code>select e.element_id, a.action_id
  from action a
  join element e on (
                     e.local_id=any(select fn_local_get_childs(a.local_id)) AND 
                     e.category_id=a.category_id)
  </code></pre>
  
  <p>Is there a way of combining the recursion used in the function in a single query?</p>