title: conditional join with input value in postgreslq function
body: <p>I have three tables:</p>
  
  <pre><code>create table id_table (
      id integer
  );
  
  insert into id_table values (1),(2),(3);
  
  create table alphabet_table (
      id integer,
      letter text
  );
  
  insert into alphabet_table values (1,'a'),(2,'b'),(3,'c');
  
  create table greek_table (
      id integer,
      letter text
  );
  
  insert into greek_table values (1,'alpha'),(2,'beta');
  </code></pre>
  
  <p>I like to create a function that join id_table with either alphabet_table or greek_table on id. The choice of the table depends on an input value specified in the function. I wrote:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION choose_letters(letter_type text)
    RETURNS table (id integer,letter text) AS $$
  BEGIN
    RETURN QUERY
    select t1.id,
           case when letter_type = 'alphabet' then t2.letter else t3.letter end as letter
    from id_table t1,
         alphabet_table t2 ,
         greek_table t3 
    where t1.id = t2.id and t1.id = t3.id;
  
  END;
  $$LANGUAGE plpgsql;
  </code></pre>
  
  <p>I ran <code>select choose_letter('alphabet')</code>. The problem with this code is that when id_table joins with alphabet_table, it does not pick up id, No 3. It seems that inner joins are done for both alphabet_table and greek_table (so it only picks up the common ids, 1 and 2). To avoid this problem, I wrote:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION choose_letters(letter_type text)
    RETURNS table (id integer, letter text) AS $$
  BEGIN
    RETURN QUERY
    select t1.id,
           case when letter_type = 'alphabet' then t2.letter else t3.letter end as letter
    from id_table t1
    left join alphabet_table t2 on t1.id=t2.id
    left join greek_table t3 on t1.id=t3.id
    where t2.letter is not null or t3.letter is not null;
  
  END;
  $$LANGUAGE plpgsql;
  </code></pre>
  
  <p>Now it pick up all the 3 ids when id_table and alphabet_table join. However, When I ran <code>select choose_letter('greek')</code>. The id no. 3 appears with null value in letter column despite the fact that I specified <code>t3.letter is not null</code> in where clause. </p>
  
  <p>What I'm looking for is that when I ran <code>select choose_letters('alphabet')</code>, the output needs to be (1,'a'), (2,'b'),(3,'c'). <code>select choose_letters('greek')</code> should produce (1,'alpha'),(2,'beta). No missing values nor null. How can I accomplish this? </p>