[Assorted-commits] SF.net SVN: assorted:[1772] shell-tools/trunk/src/bash-commons/common.bash

[<yan...@us...>]

Revision: 1772
          http://assorted.svn.sourceforge.net/assorted/?rev=1772&view=rev
Author:   yangzhang
Date:     2011-04-07 01:56:09 +0000 (Thu, 07 Apr 2011)

Log Message:
-----------
Add pg-space

Modified Paths:
--------------
    shell-tools/trunk/src/bash-commons/common.bash

Modified: shell-tools/trunk/src/bash-commons/common.bash
===================================================================
--- shell-tools/trunk/src/bash-commons/common.bash	2011-04-03 19:48:49 UTC (rev 1771)
+++ shell-tools/trunk/src/bash-commons/common.bash	2011-04-07 01:56:09 UTC (rev 1772)
@@ -704,6 +704,50 @@
   done 2>/dev/null | sort -nr | head -n 20
 }
 
+pg-space() {
+  local schema=${schema:-${1:-}}
+  case ${schema:-} in
+    '*' ) local query='select * from tables' ;;
+    '' ) local query='select * from schemas' ;;
+    * ) local query="select * from tables where _schema = '$schema'" ;;
+  esac
+  psql -c "
+    with
+      total as (
+        select sum(pg_relation_size(oid)) from pg_class where relkind = 'r'
+      ),
+      basic as (
+        select
+          n.nspname as _schema,
+          relname as _table,
+          pg_relation_size(r.oid) as bytes,
+          (100*pg_relation_size(r.oid)/(select * from total))::numeric(4,1) as pct
+        from pg_class r inner join pg_namespace n on (n.oid = relnamespace)
+        where relkind = 'r'
+      ),
+      tables as (
+        select
+          _schema,
+          _table,
+          bytes,
+          lpad(pg_size_pretty(bytes), 9) as size,
+          pct
+        from basic
+        order by bytes desc
+      ),
+      schemas as (
+        select
+          _schema,
+          sum(bytes) as bytes,
+          lpad(pg_size_pretty(sum(bytes)::int), 9) as size,
+          sum(pct) as pct
+        from basic
+        group by _schema
+        order by bytes desc
+      )
+    $query;
+  "
+}
 
 #if ! is_declared indent ; then
 #    noindent


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.