title: Function to loop through and select data from multiple tables
body: <p>I'm new to Postgres and have a database with multiple tables of the same structure. I need to select data from each table that matches certain criteria.</p>
  
  <p>I could do this with a bunch of <code>UNION</code> queries, but the number of tables I need to search can change over time, so I don't want to hard code it like that. I've been trying to develop a function that will loop through specific tables (they have a common naming convention) and return a table of records, but I'm not getting any results when I query the function. Function code is below:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION public.internalid_formaltable_name_lookup()
    RETURNS TABLE(natural_id text, name text, natural_id_numeric text) AS
  $BODY$
  DECLARE
      formal_table text;
  begin
    FOR formal_table IN
      select table_name from information_schema.tables
      where table_schema = 'public' and table_name like 'formaltable%'
    LOOP
      EXECUTE 'SELECT natural_id, name, natural_id_numeric
               FROM ' || formal_table || 
             ' WHERE natural_id_numeric IN (
                  select natural_id_numeric from internal_idlookup
                  where internal_id = ''7166571'')';
      RETURN NEXT;
   END LOOP;
   Return;
  END;
  $BODY$
    LANGUAGE plpgsql;
  </code></pre>
  
  <p>I am not getting any errors when I try to use the function, but it's not returning any rows:</p>
  
  <pre><code>SELECT * From internalid_formaltable_name_lookup();
  </code></pre>
  
  <p>Any idea where I went wrong?</p>