Ygthkb

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?

While returning rows from a loop directly, the sort order is determined by the loop. PL/pgSQL builds up the result set and you can still raise an exception to prevent the function from returning anything until the last moment (There is a code example in the manual.) But there is no way to re-order the result set before finally returning. You would have to add ORDER BY to the function call for that:

SELECT * FROM public.compare_schemas_by_table_emptiness('foo', 'bar')

ORDER  BY match;

FALSE sorts before TRUE, so non-matches come first.

But I'm really only interested in the cases where the output's "match" column is false

So don't do more work than required. This function does what you need, efficient and safe against SQL injection (unlike your original):

CREATE OR REPLACE FUNCTION public.compare_schemas_by_table_emptiness(_schema1 text, _schema2 text) 

  RETURNS TABLE(tablename text, schema1_ct bigint, schema2_ct bigint)

  LANGUAGE plpgsql AS

$func$

BEGIN

   FOR tablename IN

      SELECT quote_ident(t.tablename)  -- must be table-qualified, due to dupe in OUT parameters!

      FROM   pg_catalog.pg_tables t

      WHERE  t.schemaname = _schema1

      ORDER  BY 1

   LOOP

      EXECUTE format(

         'SELECT (SELECT count(*) FROM %1$I.%3$s)

               , (SELECT count(*) FROM %2$I.%3$s)

          WHERE  EXISTS (SELECT FROM %1$I.%3$s)

              <> EXISTS (SELECT FROM %2$I.%3$s)'

       , _schema1, _schema2, tablename)

      INTO schema1_ct, schema2_ct;



      CONTINUE WHEN schema1_ct IS NULL; -- happens when both or none have values

      RETURN NEXT;

   END LOOP;

END

$func$;

Call:

SELECT * FROM public.compare_schemas_by_table_emptiness('foo', 'bar');

Counting can be expensive with big tables, so this function only counts when required.

Identifiers may or may not require double-quoting and have to be treated like user input to be safe. See:

• SQL injection in Postgres functions vs prepared queries