Steven Steven - 5 months ago 68
SQL Question

PostgreSQL Function returning result set from dynamic tables names

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.

I'd figure I can use these "bak_" tables to see the changes that have occurred during the test like this:

SELECT * FROM employee EXCEPT SELECT * FROM bak_employee


This will show me the inserted and updated records. I'll ignore the deleted records for now.

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:

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;


But obviously this isn't going to return me any useful information. Any help would be appreciated.

Answer

You cannot return various well-known row types from the same function in the same call. A cheap fix is to cast each row type to text, so we have a common return type.

CREATE OR REPLACE FUNCTION public.show_diff()
  RETURNS SETOF text AS   -- text!!
$func$
DECLARE
  app_table text;
BEGIN
   FOR app_table 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
      RETURN NEXT ' ';
      RETURN NEXT '=== ' || app_table || ' ===';
      RETURN QUERY EXECUTE format(
        'SELECT x::text FROM (TABLE %I EXCEPT ALL TABLE %I) x'
       , app_table, 'bak_' || app_table);
   END LOOP;

   RETURN;
END
$func$ LANGUAGE plpgsql;

Call:

SELECT * FROM public.show_diff();

I had the test suggested by @a_horse at first, but after your comment I realized that there is no need for this. EXCEPT considers NULL values to be equal and shows all differences.

While being at it, I improved and simplified your solution some more. Use EXCEPT ALL: cheaper and does not run the risk of folding complete duplicates.

TABLE is just syntactical sugar.

Crucial element is the cast the row type to text (x::text).

You can even make the function work for any table - but never more than one at a time: With a polymorphic parameter type: