postgres developer snippets

list-table-size-per-schema.txt

-- Get Postgres Summary
--
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;

select table_name, pg_relation_size('<schema_id>' || '.' || table_name), count_rows(quote_ident('<schema_id>'), table_name)
from information_schema.tables
where table_schema = '<schema_id>'