Ok, Euler.
Obrigado, novamente.
criei a view abaixo, mas não consegui imaginar como substituir o DB DEFAULT
pelo nome da tablespace, porque na verdade pode ser ou a tablespace default
do banco, definida no 'create database', ou a pg_default. Tem idéia de como
resolver isso?
CREATE OR REPLACE VIEW PGS_TABLES as
SELECT upper(sh.usename) as OWNER,
upper(nm.nspname) as SCHEMA,
upper(cl.relname) as TABLE_NAME,
CASE
WHEN ts.spcname isnull then 'DB DEFAULT'
ELSE ts.spcname
END AS TABLESPACE_NAME,
cl.reltuples::NUMERIC as NUM_ROWS,
cl.relpages::NUMERIC as BLOCKS,
CASE
WHEN sum(st.stawidth)::NUMERIC is null then 0
ELSE sum(st.stawidth)::NUMERIC
END as AVG_ROW_LEN,
((cl.relpages::NUMERIC) * 8192)::NUMERIC as AVG_TABLE_SIZE,
upper(cx.relname) as DEPENDENCIES,
de.description::text
FROM pg_class cl LEFT JOIN pg_shadow sh on (cl.relowner =
sh.usesysid)
LEFT JOIN pg_tablespace ts on (cl.reltablespace =
ts.oid)
LEFT JOIN pg_namespace nm on (cl.relnamespace = nm.oid)
LEFT JOIN pg_statistic st on (cl.oid = st.starelid)
LEFT JOIN pg_depend dp on (cl.oid = dp.objid)
LEFT JOIN pg_class cx on (cx.oid = dp.refobjid)
LEFT JOIN pg_description de on (cl.oid =
de.objoid)
group by cl.oid,
cl.relkind,
cl.relname,
sh.usename,
nm.nspname,
ts.spcname,
cl.reltuples,
cl.relpages,
cx.relname,
de.description
HAVING cl.relkind = 'r';
Grato,
Sergio
2009/6/22 Euler Taveira de Oliveira <[email protected]>
> sergio nogueira escreveu:
> > talvez o comando abaixo não exiba o nome de tablespaces default de banco
> > (o que seria, talvez, estranho).
> >
> Exato, se é padrão ele não aparece, ou seja, a tabela atual *não* está em
> uma
> _tablespace_. Veja:
>
> euler=# create table foo (a int);
> CREATE TABLE
> euler=# select tablename,tablespace from pg_tables where tablename='foo';
> tablename | tablespace
> -----------+------------
> foo |
> (1 row)
>
> euler=# \! ls /tmp/foo
> PG_VERSION
> euler=# alter table foo set tablespace footbs;
> ALTER TABLE
> euler=# select tablename,tablespace from pg_tables where tablename='foo';
> tablename | tablespace
> -----------+------------
> foo | footbs
> (1 row)
>
> euler=# -- 16384 é o banco de dados 'euler'
> euler=# \! ls /tmp/foo/16384
> 16410
> euler=# select relfilenode,relname from pg_class where relname ~ 'foo';
> relfilenode | relname
> -------------+---------
> 16410 | foo
> (1 row)
>
> euler=# alter table foo set tablespace pg_default;
> ALTER TABLE
> euler=# select tablename,tablespace from pg_tables where tablename='foo';
> tablename | tablespace
> -----------+------------
> foo |
> (1 row)
>
> euler=# -- 16410 é um arquivo vazio que será removido quando for
> conveniente
> euler=# \! ls -l /tmp/foo/16384
> total 0
> -rw------- 1 euler users 0 Jun 22 01:26 16410
> euler=# select relfilenode,relname from pg_class where relname ~ 'foo';
> relfilenode | relname
> -------------+---------
> 16411 | foo
> (1 row)
>
>
> --
> Euler Taveira de Oliveira
> http://www.timbira.com/
> _______________________________________________
> pgbr-geral mailing list
> [email protected]
> https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
>
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
