Sort output of plpgsql function

Question

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?

Answer 1

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