Display size of all database in postgresql

pg_database_size(dbname) function used to return size of given database in byte format.we can use pg_size_pretty() function to convert byte into human readable format. i.e.

SELECT pg_size_pretty(pg_database_size('dbname'))

now if you would like display the size of all available database in postgres server


create or replace function pg_function_alldb_size() returns void as $$
declare
pg_cursor_dbsize cursor for select datname from pg_database;
dbname pg_database.datname%type;
size varchar(50);
begin
open pg_cursor_dbsize;
loop
fetch next from pg_cursor_dbsize into dbname;
exit when not found;
select * into size from pg_size_pretty(pg_database_size(dbname));
raise notice 'db name is % and size is % ',dbname,size;
end loop;
return ;
end;
$$ language plpgsql

• pg_cursor_dbsize is cursor which stores the all database name from pg_database table
• pg_size_pretty(pg_database_size(dbname)) it returns size of the database to the size variable.
  
• raise notice - used to display the output.