Postgresql, count rows in all tables

For count rows in each PostgreSQL table, we need to create a function and run it for all schemas

create function
cnt_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;

Run this query to get rows count for all the tables

select sum(cnt_rows) as total_no_of_rows from (select
  cnt_rows(table_schema, table_name)
from information_schema.tables
where
  table_schema not in ('pg_catalog', 'information_schema')
  and table_type='BASE TABLE') as subq;

To get rows counts tablewise

select
  table_schema,
  table_name,
  cnt_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;

Sample output