# PostgreSQL Prático/Metadados
## 12 - Metadados (Catálogo)

Metadados são dados sobre dados.
Uma consulta normal retorna informações existentes em tabelas, já uma consulta sobre os metadados retorna informações sobre os bancos, os objetos dos bancos, os campos de tabelas, seus tipos de dados, seus atributos, suas constraints, etc.
Retornar Todas as Tabelas do banco e esquema atual
Informações de Todos os Tablespaces
Retornar banco, dono, codificação, comentários e tablespace
Tabelas, donos, comentários, registros e tablespaces de um schema
Mostrar Sequences de um Esquema
Mostrar Tablespaces
Mostrar detalhes de uma function
Este exemplo mostra uma consulta que lista os nomes dos esquemas, tabelas, colunas e chaves das chaves estrangeiras, e os nomes dos esquemas, tabelas e colunas referenciadas. Exemplo tirado da lista de discussão pgsql-sql
Mostrar Esquemas e Tabelas
Mostrar Esquemas e respectivas tabelas do Banco atual:
Contar Todos os Registros de todas as tabelas de todos os bancos:
Dado o banco de dados, qual o seu diretório:
Dado a tabela, qual o seu arquivo:
Mostrar chaves primárias das tabelas do esquema public
Para visualizar como as consultas são feitas internamente via psql usamos o comando assim:
Vamos usar o banco municipios, criado com os municípios do Brasil. A tabela opt_cidades.
Veja Um Exemplo Que Retorna a Chave Primária da Tabela opt_cidades
SELECT ic.relname AS index_name, bc.relname AS tab_name, ta.attname AS column_name, i.indisunique AS unique_key, i.indisprimary AS primary_key
FROM pg_class bc, pg_class ic, pg_index i, pg_attribute ta, pg_attribute ia WHERE bc.oid = i.indrelid AND ic.oid = i.indexrelid AND ia.attrelid = i.indexrelid AND ta.attrelid = bc.oid AND bc.relname = 'opt_cidades' AND ta.attrelid = i.indrelid AND ta.attnum = i.indkey[ia.attnum-1] ORDER BY index_name, tab_name, column_name;
Retornará:
index_name | tab_name | column_name | unique_key | primary_key
Retornando o Nome do Esquema
Retorno: Esquema
Retornar nomes de bancos:
SELECT datname AS banco FROM pg_database WHERE datname != 'template0' and datname != 'template1' and datname != 'postgres' ORDER BY datname
Retornar nomes e OIDs dos bancos:
SELECT oid, datname FROM pg_database;
Dado a tabela, qual o seu arquivo: select relname, relfilenode from pg_class;
No Windows
Podemos passar parâmetros para as macros, por exemplo:
doskey /exename=psql.exe dbinfo=SELECT datname,pg_encoding_to_char(encoding) FROM pg_database WHERE datname='$1';
E então apenas passar o parâmetro na linha de comando:
postgres=# dbinfo postgres
Listar tabelas, e dono do esquema atual:
SELECT n.nspname as "Schema",
c.relname as "Tabela",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Tipo",
u.usename as "Dono"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r',)
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
Listar Tabelas
select c.relname FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r',) AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid);
SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE 'sql\_%'
Listar todas as tabelas, índices, tamanho em KB e OIDs:
VACUUM; --Executar antes este comando
SELECT c1.relname AS tabela, c2.relname AS indice,
c2.relpages * 8 AS tamanho_kb, c2.relfilenode AS arquivo
FROM pg_class c1, pg_class c2, pg_index i
WHERE c1.oid = i.indrelid AND i.indexrelid = c2.oid
UNION
SELECT relname, NULL, relpages * 8, relfilenode
FROM pg_class
WHERE relkind = 'r'
ORDER BY tabela, indice DESC, tamanho_kb;
Tabelas e Soma
SELECT tablename, SUM( size_kb )
FROM
( SELECT c1.relname AS "tablename",
c2.relpages * 8 AS "size_kb"
FROM pg_class c1, pg_class c2, pg_index i
WHERE c1.oid = i.indrelid
AND i.indexrelid = c2.oid
UNION
SELECT relname, relpages * 8
FROM pg_class
WHERE relkind = 'r' ) AS relations
GROUP BY tablename;
-- r = ordinary table, i = index, S = sequence, v = view, c = composite type, -- s = special, t = TOAST table
Tamanho em bytes de um banco:
select pg_database_size('banco');
Tamanho em bytes de uma tabela:
pg_total_relation_size('tabela')
Tamanho em bytes de tabela ou índice:
pg_relation_size('tabelaouindice')
Lista donos e bancos:
SELECT rolname as dono, datname as banco
FROM pg_roles, pg_database
WHERE pg_roles.oid = datdba
ORDER BY rolname, datname;
Nomes de bancos:
select datname from pg_database where datname not in ('template0','template1') order by 1
Nomes e colunas:
select tablename,'T' from pg_tables where tablename not like 'pg\_%' and tablename not in ('sql_features', 'sql_implementation_info', 'sql_languages', 'sql_packages', 'sql_sizing', 'sql_sizing_profiles')
union
select viewname,'V' from pg_views where viewname not like 'pg\_%'
Tamanho de esquema e índice:
SELECT nspname,
sum(relpages * cast( 8192 AS bigint )) as "table size",
sum( ( select sum(relpages)
sum ( relpages * cast( 8192 AS bigint ) + ( select sum(relpages)
FROM pg_class t, pg_namespace
WHERE relnamespace = pg_namespace.oid
and pg_namespace.nspname not like 'pg_%'
and pg_namespace.nspname != 'information_schema'
and relkind = 'r' group by nspname;
Retornando Tabelas e Seus Donos de um Esquema
SELECT n.nspname as "public",
'sequencia' WHEN 's' THEN 'especial' END as "Tipo", u.usename as "Dono"
FROM pg_catalog.pg_class c
WHERE c.relkind IN ('r',)
ORDER BY 1,2;
Retorno:
Retornando o OID e o Esquema de uma Tabela
SELECT c.oid AS "OID",
FROM pg_catalog.pg_class c
WHERE pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
Retorno:
Este exemplo mostra uma consulta que lista os esquemas, nomes das tabelas e nomes das colunas das chaves primárias de um banco de dados. Exemplo tirado da lista de discussão pgsql-sql .
CREATE TEMP TABLE teste1 (id INT, texto TEXT, PRIMARY KEY (id));
CREATE TEMP TABLE teste2 (id1 INT, id2 INT, texto TEXT, PRIMARY KEY (id1,id2));
\dt
SELECT
Este exemplo mostra uma consulta que lista os nomes dos esquemas, tabelas, colunas e chaves das chaves estrangeiras, e os nomes dos esquemas, tabelas e colunas referenciadas. Exemplo tirado da lista de discussão pgsql-sql
Retorno:
criar_sql
REFERENCES t1(id)
SELECT a.attnum, a.attname AS field, t.typname as type, a.attlen AS length, a.atttypmod-4 as lengthvar, a.attnotnull as notnull
Saída:
ID do campo, nomecampo, tipo, tamanho, nulo/nãonulo
Outros
SELECT ic.relname AS index_name, bc.relname AS tab_name, ta.attname AS column_name, i.indisunique AS unique_key, i.indisprimary AS primary_key
Saída:
nomeindex/chave, nometabela, nomecampo, unique(t/f), nomepk (t/f)
SELECT rcname as index_name, rcsrc
Saída: retorna as constraints check.
Saída: tabela, campo, tipo, tamanho (varchar)
Outros Exemplos
create table tabela_exemplo (
campo_1 integer default 5, campo_2 text default 'exemplo', campo_3 float(10),
campo_4 serial, campo_5 double precision, campo_6 int8, campo_7 Point,
campo_8 char(3), campo_9 varchar(17) );
Depois de criada a tabela vamos criar a consulta que nos retornará as informações da tabela:
SELECT
rel.nspname AS Esquema, rel.relname AS Tabela, attrs.attname AS Campo, "Type", "Default",
attrs.attnotnull AS "NOT NULL"
FROM (
SELECT c.oid, n.nspname, c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE pg_catalog.pg_table_is_visible(c.oid) ) rel
JOIN (
SELECT a.attname, a.attrelid, pg_catalog.format_type(a.atttypid, a.atttypmod) as "Type",
(SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) as "Default",
a.attnotnull, a.attnum
FROM pg_catalog.pg_attribute a WHERE a.attnum > 0 AND NOT a.attisdropped ) attrs ON (attrs.attrelid = rel.oid ) WHERE relname = 'tabela_exemplo' ORDER BY attrs.attnum;
Retorno: testes-# WHERE relname = 'tabela_exemplo' ORDER BY attrs.attnum;
Antes de tudo devemos criar um novo tipo de dado relacionado ao retorno que obteremos da função:
CREATE TYPE tabela_estrutura AS (Esquema text, Tabela text, Campo text, Tipo text, Valor text, AutoIncremento bool);
A função abaixo é definida em PL/PgSQL, linguagem procedural muito semelhante ao PL/SQL do Oracle. A função foi criada nesta linguagem devido a certas limitações que as funções em SQL possuem.
Para utilizar esta função, utilize o seguinte comando:
Retorno:
esquema | tabela | campo | tipo | valor | autoincremento
Exemplos contidos no arquivo:
/usr/local/src/postgresql-8.1.3/src/tutorial/syscat.sql
Retorno: Donos e Bancos
Retorno: Esquemas e Tabelas
Retorno: esquemas, tabelas, campos, tipos de dados
Retorno: operadores binários
Baypassar os de sistema:
and n.nspname not like 'pg\\_%' -- no catalogs
Retorno: lista todas as funções agregadas e os tipos que podem ser aplicados
Dado o banco de dados, qual o seu diretório:
Dado a tabela, qual o seu arquivo:
Exemplo que retorna índice, campo, tipo, comprimento, null, default: