Notes: PostgreSQL count rows in schemaSun 14 July 2019 #technical #postgresql #notes #programming
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.