title: Postgres function return set
body: <p>Could someone please let me know the right approach to this problem? I'm not sure if I should be using a function or a view in this case.</p>
  
  <p>I have a temporary table containing some account numbers I need to search for in a query similar to this:</p>
  
  <pre><code>SELECT
      tb1.accountNo,
      tb2.name
  FROM
      joinTable jt
      JOIN table1 tb1 ON jt.foreign-key1 = tb1.id
      JOIN table2 tb2 ON jt.foreign-key2 = tb2.id
  WHERE tb1.accountNo LIKE '%sub-string';
  </code></pre>
  
  <p>The account numbers I have in the temporary table are not exact matches, but I need to pull the info from another table. The above query works on a per-account basis, but I want something that will work in a batch.</p>
  
  <p>One approach I've tried is creating a view capturing the joins, but how could I use it with inexact account numbers across multiple accounts at once?</p>
  
  <p>Another possibility is using a function to loop through the temporary table and capture the info from the query. In this regard, my question is: how can I build up a result set or table to return from the function? Will I have to create a temporary table as part of the function and insert records into it as the loop executes? Is there a more elegant way than using a TYPE?
  Here is an example function I've been experimenting with.</p>
  
  <pre><code>CREATE TYPE accName AS (accountNo VARCHAR(255), name VARCHAR(200));
  CREATE OR REPLACE FUNCTION findMissingAccounts()
      RETURNS SETOF accName AS
      $$
      DECLARE
          tempacc RECORD;
      BEGIN
          FOR tempacc IN SELECT * from tempTable LOOP --non-complete account number
              RETURN QUERY -- RETURN QUERY or RETURN NEXT?
                  SELECT
                      tb1.accountNo,
                      tb2.name
                  FROM
                      joinTable jt
                      JOIN table1 tb1 ON jt.foreign-key1 = tb1.id
                      JOIN table2 tb2 ON jt.foreign-key2 = tb2.id
                  WHERE tb1.accountNo LIKE '%tempacc';
           END LOOP;
      RETURN;
      END;
      $$ LANGUAGE 'plpgsql' STABLE
  </code></pre>
  
  <p>Any help would be greatly appreciated.</p>