count rows for all tables in database

pg_count_all.sql

create or replace function 
count_rows(schema text, tablename text) returns integer
as
$body$
declare
  result integer;
  query varchar;
begin
  query := 'SELECT count(1) FROM ' || schema || '.' || tablename;
  execute query into result;
  return result;
end;
$body$
language plpgsql;

/*
We can invoke this function like any other postgres function with count_rows('users'). Adding our count_rows function to the original query will get us row counts for each table:
*/

select 
  table_schema,
  table_name, 
  count_rows(table_schema, table_name)
from information_schema.tables
where 
  table_schema not in ('pg_catalog', 'information_schema') 
  and table_type='BASE TABLE'
order by 3 desc

https://www.periscope.io/blog/exact-row-counts-for-every-database-table.html