dimanche 28 juin 2015

How to add company names to biggest file list in postgres

Postgres 9.1+ database contains different schema for every company named firma and company number, like firma1, firma5, firma99, firma12.

Every schema contains table with company name:

-- this table contains always exactly one row:
create table firma5.company ( company char(50) not null );

Following query list biggest tables:

select
(n.nspname||'.'||relname)::char(45) as tablename
    , pg_size_pretty(pg_total_relation_size(c.oid))::char(10) as totalsize
    , case
        when c.relkind='i' then 'index'
        when c.relkind='t' then 'toast'
        when c.relkind='r' then 'table'
        when c.relkind='v' then 'view'
        when c.relkind='c' then 'composite type'
        when c.relkind='S' then 'sequence'
        else c.relkind::text
      end ::char(14) as "type"
from
    pg_class c
    left join pg_namespace n on n.oid = c.relnamespace
    left join pg_tablespace t on t.oid = c.reltablespace
where
    (pg_total_relation_size(c.oid)>>20)>0 and c.relkind!='t'
order by
    pg_total_relation_size(c.oid) desc

This query shows company schemas like firma1, firma5 etc.

How to show company names ( firman.company.company fields ) also in this query result ? Query may return also tables from schemas other than firmaN . In this case company name column should be empty or null.

Aucun commentaire:

Enregistrer un commentaire