SQLize | PHPize | SQLtest

CREATE OR REPLACE VIEW v_used_size_per_user AS ( WITH used_per_schema_user_total_size_counter AS ( SELECT users.usename AS table_owner, pgn_cur_class.nspname AS schema_name, SUM(pg_relation_size(cur_class.oid)) AS used_per_schema_user_total_size FROM pg_class cur_class, pg_user users, pg_namespace pgn_cur_class WHERE cur_class.relowner = users.usesysid AND pgn_cur_class.oid = cur_class.relnamespace GROUP BY users.usename, pgn_cur_class.nspname ), used_user_total_size_counter AS ( SELECT users.usename AS table_owner, SUM(pg_relation_size(cur_class.oid)) AS used_user_total_size FROM pg_class cur_class, pg_user users WHERE cur_class.relowner = users.usesysid GROUP BY users.usename ) SELECT users.usename AS table_owner, pgn_cur_class.nspname AS schema_name, cur_class.relname AS table_name, pg_size_pretty(pg_relation_size(cur_class.oid)) AS table_size, pg_size_pretty(used_per_schema_user_total_size_counter.used_per_schema_user_total_size) AS used_per_schema_user_total_size, pg_size_pretty(used_user_total_size_counter.used_user_total_size) AS used_user_total_size FROM pg_class cur_class, pg_user users, pg_namespace pgn_cur_class, used_user_total_size_counter, used_per_schema_user_total_size_counter WHERE cur_class.relowner = users.usesysid AND pgn_cur_class.oid = cur_class.relnamespace AND used_user_total_size_counter.table_owner = users.usename AND used_per_schema_user_total_size_counter.table_owner = users.usename AND used_per_schema_user_total_size_counter.schema_name = pgn_cur_class.nspname ); SELECT table_owner, table_name, table_size, schema_name, used_per_schema_user_total_size, used_user_total_size FROM v_used_size_per_user ORDER BY pg_size_bytes(used_user_total_size) DESC, table_owner, pg_size_bytes(used_per_schema_user_total_size) DESC, schema_name, pg_size_bytes(table_size) DESC, table_name;