title: PostgreSQL Function returning result set from dynamic tables names
body: <p>In my database, I have the standard app tables and backup tables.  Eg.  for a table "employee", I have a table called "bak_employee".  The bak_employee table is a backup of the employee table.  I use it to restore the employee table between tests.</p>
  
  <p>I'd figure I can use these "bak_" tables to see the changes that have occurred during the test like this:</p>
  
  <pre><code>SELECT * FROM employee EXCEPT SELECT * FROM bak_employee
  </code></pre>
  
  <p>This will show me the inserted and updated records.  I'll ignore the deleted records for now.</p>
  
  <p>Now, what I would like to do is go through all the tables in my database to see if there's any changes in any of the tables.  I was thinking of doing this as a function so it's easy to call over and over.  This is what I have so far:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION public.show_diff()
    RETURNS SETOF diff_tables AS
  $BODY$
  
  DECLARE
    app_tables text;
  BEGIN
  
      FOR app_tables IN
          SELECT table_name  
  
          FROM   information_schema.tables 
  
          WHERE  table_catalog = 'myDatabase' 
            AND  table_schema = 'public'
            AND  table_name not like 'bak_%'          -- exclude existing backup tables
      LOOP
  
          -- somehow loop through tables to see what's changed something like:
          EXECUTE 'SELECT * FROM ' || app_tables || ' EXCEPT SELECT * FROM bak_' || app_tables;
  
      END LOOP;
  
      RETURN;
  END;
  $BODY$
  LANGUAGE plpgsql;
  </code></pre>
  
  <p>But obviously this isn't going to return me any useful information.  Any help would be appreciated.</p>