Notes: PostgreSQL count rows in schema

Sometimes you need to check the number of rows in more than one table. This is useful when dealing with database migration, for instance, where you need to check if the data was indeed transfered. Here is solution for counting number of rows for all tables in particular schema, which is one of the simple solutions for checking if anything happened.

Firstly, a helper function:

CREATE OR REPLACE FUNTION int_sql (query text)
LANGUAGE plpgsql
RETURNS int AS
$$
DECLARE
    val int;
BEGIN
    execute query into val;
    return val;
END;
$$

This will return the numeric value from supplied query. Now the main function:

CREATE OR REPLACE FUNCTION schemat_table_count (schema_name text)
LANGUAGE SQL
RETURNS TABLE (table_name text, table_row_count int)
AS
$$
SELECT table_name, 
       int_sql('select count(*) from '||schema_name||'.'||table_name) 
FROM information_schema.tables
WHERE 
    table_type = 'BASE TABLE' AND
    table_schema = schema_name
ORDER BY 2 DESC;
$$

I hope this might be useful for someone.