I'm creating a function in Postgres 9.6 that compares two schemas with the same tables. I want to find tables which are empty in one schema but populated in the other. I'm avoiding the
reltuples attribute found in the information schema because it is not guaranteed to be correct.
So far I have this function:
CREATE OR REPLACE FUNCTION public.compare_schemas_by_table_emptiness(schema1 text, schema2 text) RETURNS TABLE(tablename text, schema1_ct integer, schema2_ct integer, match boolean) AS $$ DECLARE schema1_tables CURSOR FOR select pg_tables.tablename from pg_tables where schemaname ~ schema1 order by tablename; schema1_ct int; schema2_ct int; BEGIN FOR table_record IN schema1_tables LOOP EXECUTE 'SELECT count(*) FROM ' || schema1 || '.' || table_record.tablename INTO schema1_ct EXECUTE 'SELECT count(*) FROM ' || schema2 || '.' || table_record.tablename INTO schema2_ct RETURN QUERY EXECUTE 'SELECT ''' || table_record.tablename || '''::text,' || schema1_ct || ',' || schema2_ct || ',' (schema1_ct >= 0) = (schema2_ct >= 0) END LOOP END; $$ LANGUAGE pgpgsql
But I'm really only interested in the cases where the output's
match column is
false, that is, one table is empty and one table is not empty. How I can I sort my output so that row where
match = false are shown first?