useful sql

disk_size.sql

select name, MB from
(
  select tbl, count(*) as MB
  from stv_blocklist
  where tbl in (
  select id
  from stv_tbl_perm)
  group by tbl
) block_list
inner join stv_tbl_perm
on block_list.tbl=stv_tbl_perm.id
group by tbl, stv_tbl_perm.name, block_list.MB
order by MB desc;

permission.sql

SELECT
  use.usename as subject, 
  nsp.nspname as namespace, 
  c.relname as item, 
  c.relkind as type, 
  use2.usename as owner, 
  c.relacl 
FROM
  pg_user use 
  cross join pg_class c 
  left join pg_namespace nsp on (c.relnamespace = nsp.oid) 
  left join pg_user use2 on (c.relowner = use2.usesysid)
WHERE
  c.relowner = use.usesysid  
  and nsp.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
  and nsp.nspname in('public','master') /** 任意のスキーマを指定 */
  and type in('r','v')
ORDER BY
  subject,namespace,item;